Forgot to append "ORDER BY price" on the second query
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Hick Gunter
Gesendet: Mittwoch, 10. April 2019 09:35
An: 'SQLite mailing list'
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:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Nik Jain
Gesendet: Sonntag, 07. April 2019 09:41
An: sqlite-users@mailinglists.sqlite.org
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
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
___
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
