SQLite

Ticket Change Details
Login
Overview

Artifact ID: 4be6b8e81ec382c55a534489a2ffc43a2419fb0c
Ticket: 7c6a01c6c5ae77de91822d812409c568e78d180e
Multi-column index is not used with IN operator
User & Date: anonymous 2013-01-15 08:18:41
Changes

  1. foundin changed to: "3.7.15.2"
  2. icomment:
    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
    
  3. login: "nobody"
  4. mimetype: "text/plain"
  5. private_contact changed to: "14cddfb49aadf411bc30f51537baea148d33c31a"
  6. severity changed to: "Critical"
  7. status changed to: "Open"
  8. title changed to: "Multi-column index is not used with IN operator"
  9. type changed to: "Performance_Issue"