Hi, 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" 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. As far as I know, the index (column1, column2, column3) is more useful than the index (column1). Why does the analyzer not like the concatened index for that query? Did I misunderstand the whole idea with the multi-column indexes? Thanks! Selen _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
