why is searching for a range of rowids in an FTS table a slow operation?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

why is searching for a range of rowids in an FTS table a slow operation?

mailing lists
Hi,

according to the documentation this is a slow query for FTS tables:

SELECT * FROM mail WHERE rowid BETWEEN 15 AND 20;

while

SELECT * FROM mail WHERE rowid=15;

is fast.
As far as I know both queries are fast on normal tables. Where is the difference / what is the reason for the performance difference?

Regards,
Hartwig

_______________________________________________
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: why is searching for a range of rowids in an FTS table a slow operation?

Clemens Ladisch
skywind mailing lists wrote:
> this is a slow query for FTS tables:
>
> SELECT * FROM mail WHERE rowid BETWEEN 15 AND 20;
>
> As far as I know both queries are fast on normal tables. Where is the difference / what is the reason for the performance difference?

Virtual table modules must implement every lookup type that needs to be fast.
The FTS module does not implement rowid lookups with inequalities.


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: why is searching for a range of rowids in an FTS table a slow operation?

Dominique Pellé
In reply to this post by mailing lists
skywind mailing lists <[hidden email]> wrote:

> Hi,
>
> according to the documentation this is a slow query for FTS tables:
>
> SELECT * FROM mail WHERE rowid BETWEEN 15 AND 20;
>
> while
>
> SELECT * FROM mail WHERE rowid=15;
>
> is fast.
> As far as I know both queries are fast on normal tables.
> Where is the difference / what is the reason for the performance difference?

That's what the FTS3/4 doc says:

== BEGIN QUOTE https://sqlite.org/fts3.html ===
CREATE VIRTUAL TABLE mail USING fts3(subject, body);

SELECT * FROM mail WHERE rowid = 15;                -- Fast. Rowid lookup.
[...]
SELECT * FROM mail WHERE rowid BETWEEN 15 AND 20;   -- Slow. Linear scan.
==

But it might not be true anymore, since
in fossil timeline, I see this checkin:

=== 2015-01-29 ===
11:52:22 [85dc12625d] *BRANCH* Optimize range constraints on the rowid
         column of fts3/4 tables even if there is no MATCH clause in the
         query. (user: dan tags: trunk)
===

And for FTS5, I don't see anything about it in the doc, but
I see this checkin in fossil:

=== 2015-06-05 ===
19:05:57 [32cbc0ed36] Make use of range constraints on the rowid field
         of an fts5 table in full-text queries. (user: dan tags: fts5)
===

Regards
Dominique
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users