Bug #64706 multiple column index/key is ignored if "IN ()" contains more than 1 item
Submitted: 20 Mar 2012 15:23 Modified: 20 Mar 2012 16:21
Reporter: Maciej Zalewski Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1.61-0+squeeze1-log OS:Linux (Debian squeeze)
Assigned to: CPU Architecture:Any
Tags: IN, INDEX, key, multiple column, Optimizer, primary key

[20 Mar 2012 15:23] Maciej Zalewski
Description:
If either secondary index or primary key is defined on more than one column then optimizer is unable to use it for certain straightforward queries, such as IN condition built entirely on PK.

Example:

CREATE TABLE test2 (
    a          INT NOT NULL,
    b	       INT NOT NULL,
    c	       INT NOT NULL,
    PRIMARY KEY (a, b)
);

EXPLAIN SELECT * FROM test2 WHERE a=1 AND b=2;
uses PK (OK)

EXPLAIN SELECT * FROM test2 WHERE (a=1 AND b=2) OR (a=4 AND b=5);
uses PK (OK)

EXPLAIN SELECT * FROM test2 WHERE (a, b) IN ( (1, 2) );
uses PK (OK)

EXPLAIN SELECT * FROM test2 WHERE (a, b) IN ( (1, 2), (3, 4) );
does not use PK (NOT OK)

How to repeat:
SELECT VERSION();
DROP TABLE test IF EXISTS;

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

INSERT INTO test VALUES (1, 'Michael', 'Jackson'),(2, 'Janet', 'Jackson'),(3, 'Michael', 'Widenius'), (4, 'Monty', 'Widenius');

EXPLAIN SELECT * FROM test WHERE last_name='Widenius' AND first_name='Michael';
EXPLAIN SELECT * FROM test WHERE (last_name='Widenius' AND first_name='Michael') OR (last_name='Jackson' AND first_name='Janet');
EXPLAIN SELECT * FROM test WHERE (last_name, first_name) IN ( ('Widenius','Michael') );
EXPLAIN SELECT * FROM test WHERE (last_name, first_name) IN ( ('Widenius','Michael'), ('Jackson','Janet') );

DROP TABLE test2 IF EXISTS;
CREATE TABLE test2 (
    a          INT NOT NULL,
    b	       INT NOT NULL,
    c	       INT NOT NULL,
    PRIMARY KEY (a, b)
);
INSERT INTO test2 VALUES (1,2,3),(4,5,6),(7,8,9);

EXPLAIN SELECT * FROM test2 WHERE a=1 AND b=2;
EXPLAIN SELECT * FROM test2 WHERE (a=1 AND b=2) OR (a=4 AND b=5);
EXPLAIN SELECT * FROM test2 WHERE (a, b) IN ( (1, 2) );
EXPLAIN SELECT * FROM test2 WHERE (a, b) IN ( (1, 2), (3, 4) );

Suggested fix:
The execution of the fourth query should also be able to use PK/index
[20 Mar 2012 15:36] Valeriy Kravchuk
Looks like a duplicate of bug #31188. Please, check.
[20 Mar 2012 16:21] Miguel Solorzano
Thank you for the feedback. Duplicate of bug: http://bugs.mysql.com/bug.php?id=31188.