Bug #50571 MySQL not using an index when using tuples in IN clause
Submitted: 24 Jan 2010 11:09 Modified: 24 Jan 2010 11:48
Reporter: Shlomo Priymak Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.1.42 OS:Any
Assigned to: CPU Architecture:Any

[24 Jan 2010 11:09] Shlomo Priymak
Description:
When using tuples in an IN clause, MySQL doesn't use an index for select and prefers a table scan for queries where it's obvious to do so.

Rewriting same query to mean the same in different form does use an index.

Provided below is a simple test case reproducing the issue.
(Same problem occurred initially on a large dataset where a full table scan chosen by the optimizer hurt performance)

How to repeat:
mysql> CREATE TABLE t (a INT, b INT, c INT, d INT, KEY IX_A_B (a,b)) ENGINE = INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO t VALUES (1,10,0,0),(2,10,0,0),(3,10,0,0),(1,20,0,0),(2,20,0,0),(3,20,0,0),
    -> (1,30,0,0),(2,30,0,0),(3,30,0,0),(1,40,0,0),(2,40,0,0),(3,40,0,0),
    -> (1,50,0,0),(2,50,0,0),(3,50,0,0),(1,60,0,0),(2,60,0,0),(3,60,0,0),
    -> (1,70,0,0),(2,70,0,0),(3,70,0,0),(1,80,0,0),(2,80,0,0),(3,80,0,0);
Query OK, 24 rows affected (0.00 sec)
Records: 24  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM t WHERE (a = 1 AND b = 10) OR (a=2 AND b=10) \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: range
possible_keys: IX_A_B
          key: IX_A_B
      key_len: 10
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM t WHERE (a,b) IN ((1,10),(2,10)) \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 24
        Extra: Using where
1 row in set (0.00 sec)

Suggested fix:
Have the optimizer treat both queries in the same manner, since they mean the same, and the one using the IN clause is simpler and more succinct.
[24 Jan 2010 11:26] Shane Bester
looks like bug #31188 ?
[24 Jan 2010 11:48] Valeriy Kravchuk
This is a duplicate of bug #31188.