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

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:

Compare these two EXPLAINs:

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.