Re: [sqlite] How to use ORDER BY on FTS5 table ?

[Nik Jain]

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 ?

[Clemens Ladisch]

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

Re: [sqlite] How to use ORDER BY on FTS5 table ?

[Nik Jain]

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 ?

[Jens Alfke]

> 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 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 
=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
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 ?

[Nik Jain]

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 ?

[Wout Mertens]

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