Multi-column index is not used with IN operator
|User & Date:||anonymous 2013-01-15 08:18:41|
- Change foundin to "220.127.116.11"
- Change icomment to:
Having an index on 3 integer columns (column1, column2, column3), the analyse command generates the stat1 and stat3 tables. I see the following statistics for this index in the sqlite_stat1 table:
"4600132 1289 1275 1"
When I execute the following SQL query, this index is not used but the query optimizer prefers to use the primary index which is on the column3, which is about 60 times slower than using the index. ( 20000 ms vs 300 ms)
"select column1, column2, column3 from table where column1 IN ( integer1, integer2, ..., integer30 ) and column2 = 1 order by column3 limit 0,200"
When I put less integer values in the IN operator OR remove the "order by" part from the query, my index is used.
OR If I create an index only on the column1, then this index is used for the original query I wrote above, which is also very efficient.
The SQLite version 3.6 does not have the same issue.
The same problem had been discussed a while ago: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg74412.html
- Change login to "nobody"
- Change mimetype to "text/plain"
- Change private_contact to "14cddfb49aadf411bc30f51537baea148d33c31a"
- Change severity to "Critical"
- Change status to "Open"
- Change title to "Multi-column index is not used with IN operator"
- Change type to "Performance_Issue"