Hello,
I would like to ask several questions regarding to SQLite FTS module and how it
uses indexes.
I have following queries:
1) A full-text query
SELECT docId FROM ftsTableWHERE ftsTable MATCH 'a*'
- gets a following query plan:
SCAN TABLE ftsTable VIRTUAL TABLE INDEX 18:
2) A query by rowid
SELECT docId FROM ftsTable WHERE docid = 10
- gets a following query plan:
SCAN TABLE ftsTable VIRTUAL TABLE INDEX 1:
So far this is as expected.
3) A query which should use a linear scan according to the SQLite documentation
(http://www.sqlite.org/fts3.html#section_1_4)
SELECT docId FROM ftsTable WHERE docId BETWEEN 20 AND 23
- gets a following query plan:
SCAN TABLE ftsTable VIRTUAL TABLE INDEX 393216:
- the documentation does not say a word about another indexes on an FTS table, so where is the index 393216 come
from?
4) The I have a query with both 'match ?' sub-clause and 'rowid=?' sub-clause. It is not clear to me which
variant will be used.
But according to definition of Full-text query it should use full-text query at first. And then? Will it use
index to rowid after full-text query is performed?
SELECT docId FROM ftsTable WHERE ftsTable MATCH 'a*' AND rowId IN (20,21,
22, 23)
- anyway from the query plan it seems that no full-text query is performed at
all - or how to interpret it?:
SCAN TABLE ftsTable VIRTUAL TABLE INDEX 1:
EXECUTE LIST SUBQUERY 1
5) When between is used instead of in, SQLite should use a linear scan, but
query plan says something else:
select docId from ftsTable where ftsTable match 'a*' and docId between 20
and 23
- query plan
SCAN TABLE ftsTable VIRTUAL TABLE INDEX 393234:
- even another index (393234) seems to be used but again, where does it come
from?
Is there any undocumented optimizations in the FTS module or did I miss some
note in the SQLite documentation?
Could you please give me a clue how to guess whether a complex FTS query will use a full-text index and which
one it will use?
And could you explain what the different numbers of indexes means? Are all
indexes really created in a DB?
Thanks for you explanation,
Milan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
