Several users have reported that certain queries with IN
predicates can't use index scans even though all the columns in
the query are indexed. What's worse, if you reformulate your
query without IN, the indexes are used. Let's take some example
query. Suppose we have a table with two indexed columns:
CREATE TABLE t1 (
col1 INTEGER,
col2 INTEGER,
...
KEY key1( col1, col2 ) );
Let's take a look at some queries that could take advantage of
the key1 index to read rows without accessing the table.
- SELECT col1, col2 FROM t1 WHERE col1 = 100;
- SELECT col1, col2 FROM t1 WHERE col1 > 100 AND col1 < 200;
- SELECT col1, col2 FROM t1 WHERE col1 > 100 AND col1 < 200 OR col1 > 300 AND col1 < 400;
- SELECT col1, col2 FROM t1 WHERE col1 = 100 AND col2 > 100 AND cold2 < 200;
…