I attach a small database where it is possible to reproduce the issue. I
deleted all irrelevant tables and all the tuples in the Message table to keep
the file size small but had run ANALYZE before doing that.
This is the query to reproduce with 3.7.15.2:
EXPLAIN QUERY PLAN
SELECT * FROM message
WHERE tag IN
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
) AND flag=1
ORDER BY id LIMIT 200;
I get this result:
selectId order from detail
0 0 0 SCAN TABLE Message USING INTEGER PRIMARY KEY
(~4601 rows)
0 0 0 EXECUTE LIST SUBQUERY 1
Hope someone can help.
- Selen
________________________________
From: Selen Schabenberger <selen_oz...@yahoo.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>; Richard
Hipp <d...@sqlite.org>
Sent: Wednesday, January 2, 2013 12:22 PM
Subject: Re: [sqlite] Multi-column index is not used with IN operator
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_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
