Bug #19123 Not using index with "IN"-Keyword

• Submitted: 16 Apr 2006 1:09
• Modified: 17 Apr 2006 1:58
• Reporter: Timo Stripf
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.11-Debian_4sarge2-log
• OS: Linux (linux debian)
• CPU Architecture: Any

[16 Apr 2006 1:09] Timo Stripf

Description:
The mysql server does not use indices if the IN-Keyword is used in WHERE cause.

On the two identical selects the first one uses indices and the second one not:

SELECT * FROM test WHERE a = 1 AND b = 2 OR a = 1 AND b = 4;
SELECT * FROM test WHERE (a,b) IN ((1,2),(1,4));

How to repeat:
CREATE TABLE test (a INT, b INT, c INT, PRIMARY KEY (a,b));

INSERT INTO test (a,b,c) VALUES(1,2,3),(2,3,4);

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

[16 Apr 2006 16:14] Hartmut Holzgraefe

this is most likely a duplicate, can't find the original right now though ...

[17 Apr 2006 1:58] Igor Babaev

This ia a duplicate of bug #16247.