skywind mailing lists <mailinglists at skywind.eu> 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
