Description:
A composite index on two columns is never chosen to filter on ROW expression over these columns using a result of a single-row subquery, though it is chosing when filtering on a ROW expression over two constants.
An index is used when filtering a single column to a result of a single-row, single-column subquery.
How to repeat:
SELECT VERSION();
DROP TABLE IF EXISTS t_selector;
CREATE TABLE t_selector (
id INT NOT NULL PRIMARY KEY,
col1 INT NOT NULL,
col2 INT NOT NULL,
val VARCHAR(50) NOT NULL,
KEY ix_selector_12 (col1, col2)
) Engine=InnoDB DEFAULT CHARSET=utf8;
INSERT
INTO t_selector
VALUES
(1, 1, 1, 'Value 1'),
(2, 2, 2, 'Value 2'),
(3, 3, 3, 'Value 3'),
(4, 4, 4, 'Value 4'),
(5, 5, 5, 'Value 5');
ANALYZE TABLE t_selector;
EXPLAIN
SELECT *
FROM t_selector
WHERE col1 = 3;
EXPLAIN
SELECT *
FROM t_selector
WHERE col1 = (SELECT 3);
EXPLAIN
SELECT *
FROM t_selector
WHERE ROW(col1, col2) = (3, 3);
EXPLAIN
SELECT *
FROM t_selector
WHERE ROW(col1, col2) = (SELECT 3, 3);
EXPLAIN
SELECT *
FROM t_selector FORCE INDEX (ix_selector_12)
WHERE ROW(col1, col2) = (SELECT 3, 3);
Output:
VERSION()
5.1.35-community
Table Op Msg_type Msg_text
test.t_selector analyze status OK
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t_selector ref ix_selector_12 ix_selector_12 4 const 1
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t_selector ref ix_selector_12 ix_selector_12 4 const 1
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t_selector ref ix_selector_12 ix_selector_12 8 const,const 1
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t_selector ALL NULL NULL NULL NULL 5 Using where
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t_selector ALL NULL NULL NULL NULL 5 Using where
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
I expected last two queries to use the index ix_selector_12, just like the third query does.
Suggested fix:
Fix the optimizer algorithm so that it can use a composite index to filter a ROW condition over two columns using a single-row subquery returning two fields.
Description: A composite index on two columns is never chosen to filter on ROW expression over these columns using a result of a single-row subquery, though it is chosing when filtering on a ROW expression over two constants. An index is used when filtering a single column to a result of a single-row, single-column subquery. How to repeat: SELECT VERSION(); DROP TABLE IF EXISTS t_selector; CREATE TABLE t_selector ( id INT NOT NULL PRIMARY KEY, col1 INT NOT NULL, col2 INT NOT NULL, val VARCHAR(50) NOT NULL, KEY ix_selector_12 (col1, col2) ) Engine=InnoDB DEFAULT CHARSET=utf8; INSERT INTO t_selector VALUES (1, 1, 1, 'Value 1'), (2, 2, 2, 'Value 2'), (3, 3, 3, 'Value 3'), (4, 4, 4, 'Value 4'), (5, 5, 5, 'Value 5'); ANALYZE TABLE t_selector; EXPLAIN SELECT * FROM t_selector WHERE col1 = 3; EXPLAIN SELECT * FROM t_selector WHERE col1 = (SELECT 3); EXPLAIN SELECT * FROM t_selector WHERE ROW(col1, col2) = (3, 3); EXPLAIN SELECT * FROM t_selector WHERE ROW(col1, col2) = (SELECT 3, 3); EXPLAIN SELECT * FROM t_selector FORCE INDEX (ix_selector_12) WHERE ROW(col1, col2) = (SELECT 3, 3); Output: VERSION() 5.1.35-community Table Op Msg_type Msg_text test.t_selector analyze status OK id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t_selector ref ix_selector_12 ix_selector_12 4 const 1 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t_selector ref ix_selector_12 ix_selector_12 4 const 1 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t_selector ref ix_selector_12 ix_selector_12 8 const,const 1 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t_selector ALL NULL NULL NULL NULL 5 Using where 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t_selector ALL NULL NULL NULL NULL 5 Using where 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used I expected last two queries to use the index ix_selector_12, just like the third query does. Suggested fix: Fix the optimizer algorithm so that it can use a composite index to filter a ROW condition over two columns using a single-row subquery returning two fields.