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 ?
sqlite-users mailing list
[hidden email] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> On Apr 5, 2019, at 2:59 PM, Nik Jain <[hidden email]> wrote:
> 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 ?
What you’re describing is basically a join. You can use SQL’s JOIN syntax to describe this as a single query, and SQLite will take care of processing it in a scalable manner:
select * from normaltable join fts on fts.id <http://fts.id/>=normaltable.id <http://normaltable.id/> where fts.col1 match ’50’ order by normaltable.price;
[syntax may be a bit off, I’m typing in a hurry!]