Milan Kříž wrote: > 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?
This is an undocumented optimization. In recent versions, FTS also optimizes docid searches with less/greater than operators. (The index number is an implementation detail.) > 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 "INDEX 1" is the full-text search. The rowid values of the returned rows are then compared (by SQLite, outside of FTS) against the list. > 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? There is only one full-text index per table. The FTS module implements a search/lookup iff the EXPLAIN QUERY PLAN output shows "VIRTUAL TABLE INDEX". Regards, Clemens _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
