I see. Query plan with order by: sqlite> explain query plan select * from productsfts p where p.attributes match '500' order by lastprice; QUERY PLAN |--SCAN TABLE productsfts AS p VIRTUAL TABLE INDEX 1: `--USE TEMP B-TREE FOR ORDER BY
Query times of both queries: sqlite> select * from productsfts p where p.attributes match '500' limit 10; Run Time: real 0.000 user 0.000137 sys 0.000125 sqlite> select * from productsfts p where p.attributes match '500' order by lastprice limit 10; Run Time: real 0.041 user 0.003760 sys 0.036068 Not sure how to proceed. I am guessing that this is a common scenario, that has a regular way of being done. If so could anyone point me to anything? Thanks On Wed, Apr 10, 2019 at 11:39 AM Clemens Ladisch <[email protected]> wrote: > PM Nik Jain wrote: > > A SCAN is being performed on a fts5 table. I am not sure but I > > think that means no index. > > > > sqlite> explain query plan select * from productsfts p where > p.attributes match '50' limit 6; > > `--SCAN TABLE productsfts AS p VIRTUAL TABLE INDEX 1: > > Everything except "INDEX 0" means that it is not a plain table scan, > but that the virtual table module does its own filtering. > > > Regards, > Clemens > _______________________________________________ > 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
