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