Reordering the index worked but actually I need the index in (Tag, Flag, Id)
order. The Flag column has a very low cardinality, and my queries are mostly
filtering Tag & Flag or the Tag alone.
When I drop the index on the Flag, and leave the index on (Tag, Flag, Id), the
query planner still chooses the primary key on the Id column.
-Selen
From: Richard Hipp <d...@sqlite.org>
To: Selen Schabenberger <selen_oz...@yahoo.com>; General Discussion of SQLite
Database <sqlite-users@sqlite.org>
Sent: Friday, December 14, 2012 3:09 PM
Subject: Re: [sqlite] Multi-column index is not used with IN operator
On Thu, Dec 13, 2012 at 10:06 AM, Selen
Schabenberger <selen_oz...@yahoo.com> wrote:
Hi All,
>
>I am observing some strange behaviour on my database when I execute a query
>with an IN operator having more than "22" expressions. My table structure
>looks basically as follows:
>
>CREATE TABLE "Messages" ("Id" INTEGER PRIMARY KEY NOT NULL, "Tag" INTEGER NOT
>NULL, "Flag" INTEGER )
>
>
>I have a multi-column index on (Tag, Flag, Id) as well as a single column
>index on the Flag column.
>
My guess is that the single-column index on Flag is misleading the query
optimizer. You can probably fix this by either (1) running ANALYZE or (2)
adding a "+" in front of the "Flag" column name in the WHERE clause of your
query, like this: "... +Flag=1 ..."
--
D. Richard Hipp
d...@sqlite.org
________________________________
From: Simon Slavin <slav...@bigfraud.org>
To: Selen Schabenberger <selen_oz...@yahoo.com>; General Discussion of SQLite
Database <sqlite-users@sqlite.org>
Sent: Friday, December 14, 2012 2:11 PM
Subject: Re: [sqlite] Multi-column index is not used with IN operator
On 13 Dec 2012, at 3:06pm, Selen Schabenberger <selen_oz...@yahoo.com> wrote:
>> explain query plan Select Messages.Id from Messages where (Tag in ( 1146883,
>> 1146884, 1146886, 1146888, 1146892, 1146894, 1146896, 1146898, 1146920,
>> 1146922, 1147912, 1147914, 1147968, 1147970, 1147976, 1147978, 1148012,
>> 1148015, 1148016, 1148018, 1148020, 1148022, 1148040, 1148042, 1148079,
>> 1148136, 1148138, 1148191, 1148232, 1148234, 1167643, 1167659, 1167660,
>> 1167663, 1167667, 1167671, 1167675 ) and Flag=1) order by Messages.Id limit
>> 0, 100
Just out of interest, that form where you go
... WHERE (Tag in (a,b,c) AND Flag=1) ...
Can you try a version where it uses instead
... WHERE Tag in (a,b,c) AND Flag=1 ...
or
... WHERE (Tag in (a,b,c)) AND (Flag=1) ...
I'm trying to predict how the parsing works here but I don't know whether I
figured it out. Another thing to try is to reverse the order of your index
> I have a multi-column index on (Tag, Flag, Id) as well as a single column
> index on the Flag column.
Can you add a new one, or change your existing one, to (Flag, Tag, Id) ?
> Are there any restrictions with the IN operator?
Nothing that you got anywhere close to. It's meant to be able to handle text
and lists far longer than that.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
