SQLite

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

This sounds like a problem, but there are details missing from this bug report. Please raise this on the mailing list again.

Running the following with 3.7.15.2:

CREATE TABLE tbl(c1, c2, c3 INTEGER PRIMARY KEY);
CREATE INDEX idx2 ON tbl(c1, c2, c3);

ANALYZE sqlite_master;
INSERT INTO sqlite_stat1 VALUES('tbl', 'idx2', '4600132 1289 1275 1');

EXPLAIN QUERY PLAN
SELECT c1, c2, c3 FROM tbl
WHERE c1 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 c2=1
ORDER BY c3 LIMIT 200;

produces:

0|0|0|SEARCH TABLE tbl USING COVERING INDEX idx2 (c1=? AND c2=?) (~3510 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|USE TEMP B-TREE FOR ORDER BY