Bug #46288 Index is not used for equality condition on single-row, multiple column subquery
Submitted: 18 Jul 2009 19:12 Modified: 18 Jul 2009 19:31
Reporter: Alex Bolenok Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1.35-community, 5.1.36 OS:Microsoft Windows
Assigned to:
Tags: INDEX, row, subquery
Triage: Triaged: D3 (Medium)

[18 Jul 2009 19:12] Alex Bolenok
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.
[18 Jul 2009 19:31] Valerii Kravchuk
Thank you for the problem report. Verified just as described.