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 products
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 "INDE
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 pr
> On Apr 5, 2019, at 2:59 PM, Nik Jain 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 lar
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
`--SCA
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 b
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 feelin
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
8 matches
Mail list logo
