Re: [sqlite] [EXTERNAL] Re: 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); Same time here.. And it doesn't seem to matter if there is an index. Its always "`--USE TEMP B-TREE FOR ORDER BY" Cross join is wayy slower btw. I guess its just not possible for it to use the index when orderingBy with a fts table.. Gotta move one for me. And please consider adding a forum. Its pretty annoying to receive 10 mails a day on a variety of topics just to follow a discussion. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use ORDER BY on FTS5 table ?
I see. Query plan with order by: sqlite> explain query plan select * from productsfts p where p.attributes match '500' order by lastprice; QUERY PLAN |--SCAN TABLE productsfts AS p VIRTUAL TABLE INDEX 1: `--USE TEMP B-TREE FOR ORDER BY Query times of both queries: sqlite> select * from productsfts p where p.attributes match '500' limit 10; Run Time: real 0.000 user 0.000137 sys 0.000125 sqlite> select * from productsfts p where p.attributes match '500' order by lastprice limit 10; Run Time: real 0.041 user 0.003760 sys 0.036068 Not sure how to proceed. I am guessing that this is a common scenario, that has a regular way of being done. If so could anyone point me to anything? Thanks On Wed, Apr 10, 2019 at 11:39 AM Clemens Ladisch wrote: > PM Nik Jain wrote: > > A SCAN is being performed on a fts5 table. I am not sure but I > > think that means no index. > > > > sqlite> explain query plan select * from productsfts p where > p.attributes match '50'limit 6; > > `--SCAN TABLE productsfts AS p VIRTUAL TABLE INDEX 1: > > Everything except "INDEX 0" means that it is not a plain table scan, > but that the virtual table module does its own filtering. > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use ORDER BY on FTS5 table ?
Anybody ? On Mon, Apr 8, 2019 at 9:03 PM Nik Jain wrote: > ok. I investigated further, and it seems my problem is something else > entirely! A SCAN is being performed on a fts5 table. I am not sure but I > think that means no index. > > Query plan: > sqlite> explain query plan select * from productsfts p where > p.attributes match '50'limit 6; > QUERY PLAN > `--SCAN TABLE productsfts AS p VIRTUAL TABLE INDEX 1: > Run Time: real 0.000 user 0.48 sys 0.35 > > > Schema: > CREATE VIRTUAL TABLE productsfts using fts5( > attributes , > lastprice, > id , > categoryid, > brandid > ) > /* productsfts(attributes,lastprice,id,categoryid,brandid) */; > > Is this normal ? When A order by clause is added to above query. It always > shows a "Using temp b-tree for order by" > > On Mon, Apr 8, 2019 at 1:44 PM Wout Mertens > wrote: > >> 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 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 >> > sqlite-users@mailinglists.sqlite.org >> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use ORDER BY on FTS5 table ?
ok. I investigated further, and it seems my problem is something else entirely! A SCAN is being performed on a fts5 table. I am not sure but I think that means no index. Query plan: sqlite> explain query plan select * from productsfts p where p.attributes match '50'limit 6; QUERY PLAN `--SCAN TABLE productsfts AS p VIRTUAL TABLE INDEX 1: Run Time: real 0.000 user 0.48 sys 0.35 Schema: CREATE VIRTUAL TABLE productsfts using fts5( attributes , lastprice, id , categoryid, brandid ) /* productsfts(attributes,lastprice,id,categoryid,brandid) */; Is this normal ? When A order by clause is added to above query. It always shows a "Using temp b-tree for order by" On Mon, Apr 8, 2019 at 1:44 PM Wout Mertens wrote: > 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 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 > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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
[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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
