How to use ORDER BY on FTS5 table ?

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

How to use ORDER BY on FTS5 table ?

Nik Jain
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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to use ORDER BY on FTS5 table ?

Jens Alfke-2


> 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!]

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users