Clemens Ladisch wrote:
Milan Kříž wrote:
Clemens Ladisch wrote:
"INDEX 1" is the full-text search.
Sorry, that's wrong.
So does it mean that the full-text search is not performed for the following
query at all?
And that only the docId index is used to get entries in the IN sub-clause
and then a linear scan with a comparison to 'a*' is done?
query:
SELECT docId FROM ftsTable WHERE ftsTable MATCH 'a*' AND rowId IN (20,21, 22,
23)
query plan:
SCAN TABLE ftsTable VIRTUAL TABLE INDEX 1:
EXECUTE LIST SUBQUERY 1
Is it possible to force SQLite to use the full-text search instead of the rowid
search?
I can think only about something like this:
select docId from (
select docId from ftsTable where ftsTable match 'a*'
) where docId in (21, 22, 23, 24)
query plan:
SCAN TABLE nameFtsTable VIRTUAL TABLE INDEX 18:
EXECUTE LIST SUBQUERY 1
It looks much better. But what does the 'EXECUTE LIST SUBQUERY 1' mean? You wrote that returned values are
compared by SQLite (outside of FTS).
But does it use some index (rowid index) or is it impossible for SQLite to use an index on the same table (even
if the first one - full-text index - was used in a subquery)?
Of course, in my real use-case I have much more complex docId condition and I have a lot of entries in an FTS
table (about million entries),
so I would like the full-text search to prune the results first and then filter results using docId. FTS
condition is also much more complex than just 'a*' so I expect that
many results will be filtered out by the full-text query.
Thanks,
Milan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
