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.
If I execute the following query on this table, the Messages table is scanned
using the primary key and the results are returned in 20 seconds.
> 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
>> Scan table Messages using integer primary key
>> Execute list subquery1
However if I reduce the number of expressions in the IN operator, the index on
the (Tag, Flag, Id) is used.
> explain query plan Select Messages.MessageId from Messages where (AspTag in (
> 1146883, 1146888, 1146892, 1146894, 1146896, 1146898, 1146920, 1146922,
> 1147912, 1147914, 1147968, 1147970, 1147976, 1147978, 1148012, 1148015,
> 1148016, 1148018, 1148020, 1148022, 1148040, 1148042) and PduFlag=1) order
> by MessageId limit 0, 100
>> Search table Messages using covering index IDX_TAG_FLAG_ID
>> Execute list subquery1
>> Use temp b-tree for order by
If I rewrite the first query in multiple IN operators connecting with OR then I
can again use my multi-column index and it is much more efficient than the full
table scan.
> explain query plan Select Messages.Id from Messages where (Tag in (
> 1148138,1148191, 1148232, 1148234, 1167643, 1167659,1146883, 1146884,
> 1146886, 1146888, 1146892, 1146894, 1146896, 1146898, 1146920, 1146922,
> 1147912, 1147914, 1147968, 1147970, 1147976, 1147978 ) or Tag in (1148012,
> 1148015, 1148016, 1148018, 1148020, 1148022, 1148040, 1148042, 1148079,
> 1148136, 1167660, 1167663, 1167667, 1167671, 1167675) ) and Flag=1 order by
> Messages.Id limit 0, 100
>> Execute list subquery1
>> Search table Messages using covering index IDX_TAG_FLAG_ID
>> Execute list subquery1
>> Search table Messages using covering index IDX_TAG_FLAG_ID
>> Use temp b-tree for order by
Does anybody have any idea why it behaves so? Are there any restrictions with
the IN operator?
Regards,
-Selen
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
