These files demonstrate a bug in MySQL/InnoDB that has existed for at least 3 years across a range of versions.

Summary

Using a an IN(...) clause that contains a numeric value (quoted or not) that's outside the range of storable values for the column, MySQL will perform a full table scan if there are other quoted numeric values in the list. On large tables this can be very, very bad.

Usage

Create the simple table:

mysql -u user -ppassword test < schema.sql

Populate it:

mysql -u user -ppassword test < insert.sql

You now have a table called test_table that contains 10,000 rows that look like (1, 1), (2, 2), (3, 3), ... (10000, 10000).

To see the bug:

mysql -u user -ppassword test < query.sql

id  select_type table   type    possible_keys   key key_len ref rows Extra
1   SIMPLE  test_table  ALL PRIMARY NULL    NULL    NULL    10749 Using where
id  select_type table   type    possible_keys   key key_len ref rows Extra
1   SIMPLE  test_table  ALL PRIMARY NULL    NULL    NULL    10749 Using where
id  select_type table   type    possible_keys   key key_len ref rows Extra
1   SIMPLE  test_table  range   PRIMARY PRIMARY 4   NULL    4 Using where

Note that MySQL wants to scan an estimated 10,749 rows in the first two example. And the "ALL" in the type column is a bad sign! The last query gets it right, though the 4 should probably be a 3.

To see what it should look like:

mysql -u user -ppassword test < good_query.sql

id  select_type table   type    possible_keys   key key_len ref rows Extra
1   SIMPLE  test_table  range   PRIMARY PRIMARY 4   NULL    3   Using where

Note that we now have "range" in the type column and it correctly sees the 3 matching rows immediately.