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
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