Anybody ? On Mon, Apr 8, 2019 at 9:03 PM Nik Jain <[email protected]> wrote:
> ok. I investigated further, and it seems my problem is something else > entirely! A SCAN is being performed on a fts5 table. I am not sure but I > think that means no index. > > Query plan: > sqlite> explain query plan select * from productsfts p where > p.attributes match '50' limit 6; > QUERY PLAN > `--SCAN TABLE productsfts AS p VIRTUAL TABLE INDEX 1: > Run Time: real 0.000 user 0.000048 sys 0.000035 > > > Schema: > CREATE VIRTUAL TABLE productsfts using fts5( > attributes , > lastprice, > id , > categoryid, > brandid > ) > /* productsfts(attributes,lastprice,id,categoryid,brandid) */; > > Is this normal ? When A order by clause is added to above query. It always > shows a "Using temp b-tree for order by" > > On Mon, Apr 8, 2019 at 1:44 PM Wout Mertens <[email protected]> > wrote: > >> You need to create an index on both columns at once or the indexes can't >> be >> used. Try "EXPLAIN QUERY PLAN SELECT ...your query here" to see if indexes >> are being used. >> >> Wout. >> >> On Sun., Apr. 7, 2019, 9:41 a.m. Nik Jain <[email protected]> wrote: >> >> > Have a fts5 table with 2 indexed columns. Where the idea is to match by >> > one col and sort using the other one. Something like : >> > >> > "select id from fts where col1 match '50' order by price " >> > >> > This is slow. 0.07 seconds. Removing the order by clause - 0.001 >> seconds. >> > How do I fix this ? I have a feeling I am using this fts table in an >> > incorrect way. One way is to run 2 queries. First on the fts table, to >> > return ids. Second on the regular table with the order by clause. " >> select >> > * from normaltable where id in (Ids) order by price " . This approach >> is >> > fast. But the id list could be large sometimes. >> > Any other way ? >> > Thanks >> > >> > PS: This is my second attempt at mailing lists. Not sure if this one >> will >> > go through. >> > _______________________________________________ >> > sqlite-users mailing list >> > [email protected] >> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
