How to use ORDER BY on FTS5 table ?

classic Classic list List threaded Threaded
10 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

PS: This is my second attempt at mailing lists. Not sure if this one will
go through.
_______________________________________________
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 ?

wmertens
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
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 ?

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.000048 sys 0.000035


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 <[hidden email]> 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 <[hidden email]> 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
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 ?

Nik Jain
Anybody ?

On Mon, Apr 8, 2019 at 9:03 PM Nik Jain <[hidden email]> 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.000048 sys 0.000035
>
>
> 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 <[hidden email]>
> 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 <[hidden email]> 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
>> > [hidden email]
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
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 ?

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

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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
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: [EXTERNAL] How to use ORDER BY on FTS5 table ?

Hick Gunter
In reply to this post by Nik Jain
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);

Both statements will materialize the fts query and then retrieve records from the normal table in the desired order. If you only need "a few" columns fort he specific query, you can also append these to the index (build a "covering index") to allow sqlite to fulfill the field list from the index without accessing the table.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Nik Jain
Gesendet: Sonntag, 07. April 2019 09:41
An: [hidden email]
Betreff: [EXTERNAL] [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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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: [EXTERNAL] How to use ORDER BY on FTS5 table ?

Hick Gunter
Forgot to append "ORDER BY price" on the second query

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Hick Gunter
Gesendet: Mittwoch, 10. April 2019 09:35
An: 'SQLite mailing list' <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] 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);

Both statements will materialize the fts query and then retrieve records from the normal table in the desired order. If you only need "a few" columns fort he specific query, you can also append these to the index (build a "covering index") to allow sqlite to fulfill the field list from the index without accessing the table.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Nik Jain
Gesendet: Sonntag, 07. April 2019 09:41
An: [hidden email]
Betreff: [EXTERNAL] [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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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: [EXTERNAL] Re: How to use ORDER BY on FTS5 table ?

Hick Gunter
In reply to this post by Nik Jain
The time difference is easily explained:

The first query stops after it has retrieved 10 matching records from the fts table.

The second query has to retrieve all of the matching records, sort them in the desired sort order, and then discard all but the first 10 records.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Nik Jain
Gesendet: Mittwoch, 10. April 2019 08:43
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] 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
sqlite> 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
sqlite> 10;
Run Time: real 0.000 user 0.000137 sys 0.000125

sqlite> select * from productsfts p where p.attributes match '500' order
sqlite> 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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: [EXTERNAL] Re: How to use ORDER BY on FTS5 table ?

Nik Jain
 >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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users