You need to create an index on both columns at once or the indexes can't be used. Try "EXPLAIN QUERY PLAN SELECT ...your query here" to see if indexes are being used.
Wout. On Sun., Apr. 7, 2019, 9:41 a.m. Nik Jain <[email protected]> wrote: > 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 > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
