Bug #45145 Index is not used when an IN expression contains a single-value subquery
Submitted: 27 May 2009 22:17 Modified: 5 Jun 2009 21:27
Reporter: Alex Bolenok Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.2 OS:Windows
Assigned to: CPU Architecture:Any
Tags: IN, INDEX, range, subquery

[27 May 2009 22:17] Alex Bolenok 
Description:
When an IN expression on an indexed column contains a single-value subquery, the optimizer is not able to use the index.

How to repeat:
SELECT VERSION();
CREATE TABLE t_selector (id INT NOT NULL PRIMARY KEY, value VARCHAR(50) NOT NULL) ENGINE=InnoDB;
INSERT
INTO    t_selector
VALUES
        (1, 'Value 1'),
        (2, 'Value 2'),
        (3, 'Value 3'),
        (4, 'Value 4');
EXPLAIN
SELECT  *
FROM    t_selector so FORCE INDEX (PRIMARY)
WHERE   so.id = 1
        OR so.id = (SELECT si.id FROM t_selector si WHERE si.id = 2);

1, 'PRIMARY', 'so', 'range', 'PRIMARY', 'PRIMARY', '4', '', 2, 'Using where'
2, 'SUBQUERY', 'si', 'const', 'PRIMARY', 'PRIMARY', '4', '', 1, 'Using index'

EXPLAIN
SELECT  *
FROM    t_selector so FORCE INDEX (PRIMARY)
WHERE   so.id IN (1, (SELECT si.id FROM t_selector si WHERE si.id = 2));

1, 'PRIMARY', 'so', 'ALL', '', '', '', '', 4, 'Using where'
2, 'SUBQUERY', 'si', 'const', 'PRIMARY', 'PRIMARY', '4', 'const', 1, 'Using index'

I expected the second query to use the PRIMARY KEY, just as the first query does.

However, the index is not used even despite FORCE INDEX (PRIMARY) explicitly provided.

Suggested fix:
Fix the optimizer algorithm so that it uses RANGE access for such a query.
[4 Jun 2009 6:21] Valeriy Kravchuk 
Thank you for the problem report. Please, specify the exact version, x.y.z, you had used. I doubt it is 5.2.
[5 Jun 2009 19:32] Alex Bolenok 
SELECT VERSION();

'5.2.0-falcon-alpha-community-nt-log'
[5 Jun 2009 19:56] Valeriy Kravchuk 
This is a very old version. 5.2 became 6.0 later, so, please, check with the newer version, 6.0.11, and inform about the results.
[5 Jun 2009 21:27] Alex Bolenok 
Right, seems to be corrected in 6.0