Optimizing IN() queries against a compound index

Optimizing IN() queries against a compound index:
Unfortunately, MySQL 5.5 doesn’t generate a very good query execution plan for IN() queries against a compound (multi-column) index, such as the following query that should be able to use the 2-column primary key:

explain select * from tbl1
where (col1, col2) in (
      (732727758,102),(732728118,102),(732728298,102),(732728478,102),
      (732735678,102),(962074728,102),(964153098,102),(2027956818,102),
      (2034233178,102),(2034233538,102))\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1379
        Extra: Using where

Queries such as this should usually be rewritten to a form such as the following, which accesses only the 10 rows specified instead of scanning the table:

explain select * from tbl1
where (col1=732727758 and col2=102)
   OR (col1=732728118 and col2=102)
   OR (col1=732728298 and col2=102)
   OR (col1=732728478 and col2=102)
   OR (col1=732735678 and col2=102)
   OR (col1=962074728 and col2=102)
   OR (col1=964153098 and col2=102)
   OR (col1=2027956818 and col2=102)
   OR (col1=2034233178 and col2=102)
   OR (col1=2034233538 and col2=102)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl1
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 10
          ref: NULL
         rows: 10
        Extra: Using where

Further Reading:

• Why won’t MySQL use the best index in a join?
• How to avoid an extra index scan in MySQL
• The difference between a unique index and primary key in MySQL
• A case study in profiling queries in MySQL
• Two subtle bugs in OUTER JOIN queries

DIGITAL JUICE