Hi Richard,
I tested the whole scenario one more time with the new SQLite version. As you
suggested I put a plus sign in front of the Flag column and that really made
the query much faster by using the multi column index (Tag, Flag, Id) instead
of the primary index on the Id column. However what I don't get is, I actually
had removed that single column index on the Flag before and run ANALZE. How
come the query optimizer makes another decision when I put a + in front of a
column which is not indexed alone?
Is there another way to improve this query, other than using the + sign? I
would really appreciate any suggestions.
Happy new year!
Regards,Selen
--- On Fri, 12/14/12, Richard Hipp <d...@sqlite.org> wrote:
From: Richard Hipp <d...@sqlite.org>
Subject: Re: [sqlite] Multi-column index is not used with IN operator
To: "Selen Schabenberger" <selen_oz...@yahoo.com>, "General Discussion of
SQLite Database" <sqlite-users@sqlite.org>
Date: Friday, December 14, 2012, 3:09 PM
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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
