Don’t put a NULL in the IN clause in 5.1

April 8, 2009 · Posted in MySQL · Comment 

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.

Tags: <, , >