Re: [sqlite] FTS full-text query vs. query by rowid

2014-08-28 Thread Milan Kříž
Dne 28.8.2014 16:48, Clemens Ladisch napsal(a): Then try with 3.8.6. Ouuu . .sorry again. I have tested it with 3.8.6 and the query plan looks ok now. SCAN TABLE nameftsTable VIRTUAL TABLE INDEX 18: EXECUTE LIST SUBQUERY 1 But I also tested it with my version again and I'm getting the

Re: [sqlite] FTS full-text query vs. query by rowid

2014-08-28 Thread Clemens Ladisch
Milan Kříž wrote: > Dne 28.8.2014 14:54, Clemens Ladisch napsal(a): >> Milan Kříž wrote: >>> So does it mean that the full-text search is not performed for the >>> following query at all? >> >> No, it means that you are using a different version. > > But I still cannot understand that query plan.

Re: [sqlite] FTS full-text query vs. query by rowid

2014-08-28 Thread Milan Kříž
Dne 28.8.2014 14:54, Clemens Ladisch napsal(a): Milan Kříž wrote: So does it mean that the full-text search is not performed for the following query at all? SELECT docId FROM ftsTable WHERE ftsTable MATCH 'a*' AND rowId IN (20,21, 22, 23) SCAN TABLE ftsTable VIRTUAL TABLE INDEX 1:

Re: [sqlite] FTS full-text query vs. query by rowid

2014-08-28 Thread Clemens Ladisch
Milan Kříž wrote: > So does it mean that the full-text search is not performed for the following > query at all? > > SELECT docId FROM ftsTable WHERE ftsTable MATCH 'a*' AND rowId IN (20,21, 22, > 23) > > SCAN TABLE ftsTable VIRTUAL TABLE INDEX 1: > EXECUTE LIST SUBQUERY 1 No, it means t

Re: [sqlite] FTS full-text query vs. query by rowid

2014-08-28 Thread Milan Kříž
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 th

Re: [sqlite] FTS full-text query vs. query by rowid

2014-08-28 Thread Clemens Ladisch
Milan Kříž wrote: > Clemens Ladisch wrote: >> "INDEX 1" is the full-text search. Sorry, that's wrong. The idxNum value is determined as follows: (see fts3Int.h) /* ** The Fts3Cursor.eSearch member is always set to one of the following. ** Actualy, Fts3Cursor.eSearch can be greater than or equal

Re: [sqlite] FTS full-text query vs. query by rowid

2014-08-28 Thread Milan Kříž
Clemens Ladisch wrote: 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 q

Re: [sqlite] FTS full-text query vs. query by rowid

2014-08-28 Thread Clemens Ladisch
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

[sqlite] FTS full-text query vs. query by rowid

2014-08-27 Thread Milan Kříž
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 r