There seems to be an optimizer problem in 5.1, if you put a NULL in the IN clause of a SELECT. For example, given the following table:
CREATE TABLE foo (
a INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (a)
);
Compare these two EXPLAINs:
mysql> EXPLAIN * FROM foo WHERE a IN (160000, 160001, 160002)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: foo
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 3
Extra: Using where
1 row in set (0.06 sec)
mysql> EXPLAIN SELECT * FROM foo WHERE a IN (NULL, 160000, 160001, 160002)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: foo
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 327680
Extra: Using where
1 row in set (0.00 sec)
In the query with the NULL, it does a full table scan. So, if you’ve run into this problem under MySQL 5.1, the workaround is to remove the NULL. This doesn’t affect MySQL 4.x or 5.0.
You can also follow along with Bug #33139.