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