On 01/16/2013 05:13 PM, Selen Schabenberger wrote:
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.
Mailing list does not allow attachments. Can you either upload the
db somewhere, or include the output of ".dump" in the body of the
message if it is small enough? Thanks.
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 ..."
_______________________________________________ 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
