The time difference is easily explained: The first query stops after it has retrieved 10 matching records from the fts table.
The second query has to retrieve all of the matching records, sort them in the desired sort order, and then discard all but the first 10 records. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Nik Jain Gesendet: Mittwoch, 10. April 2019 08:43 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] Re: [sqlite] How to use ORDER BY on FTS5 table ? I see. Query plan with order by: sqlite> explain query plan select * from productsfts p where sqlite> 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 sqlite> 10; Run Time: real 0.000 user 0.000137 sys 0.000125 sqlite> select * from productsfts p where p.attributes match '500' order sqlite> 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 <clem...@ladisch.de> 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
