Forgot to append "ORDER BY price" on the second query -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:[email protected]] Im Auftrag von Hick Gunter Gesendet: Mittwoch, 10. April 2019 09:35 An: 'SQLite mailing list' <[email protected]> Betreff: Re: [sqlite] [EXTERNAL] How to use ORDER BY on FTS5 table ?
If the original table has an index starting with price: WITH idlist(id) AS (SELECT id FROM fts WHERE col1 MATCH '50') SELECT * FROM table CROSS JOIN idlist ON (idlist.id=table.id) ORDER BY price; or WITH idlist(id) AS (SELECT id FROM fts WHERE col1 MATCH '50') SELECT * FROM table WHERE id IN (SELECT id FROM idlist); Both statements will materialize the fts query and then retrieve records from the normal table in the desired order. If you only need "a few" columns fort he specific query, you can also append these to the index (build a "covering index") to allow sqlite to fulfill the field list from the index without accessing the table. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:[email protected]] Im Auftrag von Nik Jain Gesendet: Sonntag, 07. April 2019 09:41 An: [email protected] Betreff: [EXTERNAL] [sqlite] How to use ORDER BY on FTS5 table ? 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 ___________________________________________ 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 [email protected] 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
