Description:
The query "DELETE FROM test WHERE (key1, key2) IN ((12345, 12345))" takes unusually long and does not seem to use an index, while "SELECT * FROM test WHERE (key1, key2) IN ((12345, 12345))" is very fast. The second query seems to optimize to (key1=12345 AND key2=12345), but the DELETE-query doesn't.
As far as I can see the conditions for this problem occurring are:
- InnoDB storage engine
- key with more than one value
- DELETE-query
- WHERE-clause using ... IN (...) syntax
See http://forums.mysql.com/read.php?22,249112,249112 for details.
How to repeat:
CREATE TABLE test (
key1 INT,
key2 INT,
PRIMARY KEY (key1, key2)
) ENGINE=InnoDB;
-- Fill table with 1,000,000 random records
SELECT * FROM test WHERE key1=12345 AND key2=12345;
Empty set (0.00 sec)
SELECT * FROM test WHERE (key1, key2) IN ((12345, 12345));
Empty set (0.00 sec)
DELETE FROM test WHERE key1=12345 AND key2=12345;
Query OK, 0 rows affected (0.00 sec)
DELETE FROM test WHERE (key1, key2) IN ((12345, 12345));
Query OK, 0 rows affected (2.16 sec) -- Takes ridiculous long, doesn't matter if record exists or not.
