Bug #40029 to make possible using IN() operation with multi-key index.
Submitted: 14 Oct 2008 18:09 Modified: 14 Oct 2008 18:23
Reporter: Aleksey Kishkin Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.0 OS:Any
Assigned to: CPU Architecture:Any

[14 Oct 2008 18:09] Aleksey Kishkin
now request

select * from table where (PK1, PK2) in ((A, B), (C, D));

uses full scan, despite the fact the fileds PK1 and PK2 are parts of primary key and 

select * from table where (PK1, PK2) = (A, B) OR (PK1, PK2) = (C, D);

uses primary key.

I believe optimizer must be smart enough and rewrite first query to second one.

How to repeat:
run explain against select * from table where (PK1, PK2) in ((A, B), (C, D)); where primary key is compound like (PK1,PK2)..

Suggested fix:
improve optimizer
[14 Oct 2008 18:21] Valeriy Kravchuk
Is this a duplicate of http://bugs.mysql.com/bug.php?id=31188?