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
