MySQL Forums
Forum List  »  InnoDB

Index not used in certain DELETE-queries?
Posted by: Maarten van Schaik
Date: February 21, 2009 06:23PM

I have a quite specific problem using DELETE queries. I have a table with a double primary key, like this:
CREATE TABLE test (
key1 INT,
key2 INT,
PRIMARY KEY (key1, key2)
) ENGINE=InnoDB;

I fill it with random numbers so it has about a million records. When selecting records everything is fine:
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)

Now for the DELETE's:
DELETE FROM test WHERE key1=12345 AND key2=12345;
Query OK, 0 rows affected (0.00 sec)

But when using DELETE using the second syntax:
DELETE FROM test WHERE (key1, key2) IN ((12345, 12345));
Query OK, 0 rows affected (1.06 sec)

This query is structurally extremely slow. Tried multiple versions of MySQL (5.0, 5.1), OS'es (Linux, FreeBSD), but no difference there. The conditions I have found to make this happen are:
- InnoDB storage engine
- key with more than one value
- DELETE-query
- WHERE-clause using ... IN (...) syntax

Am I doing something wrong? Is this a bug? Is there a way to display the query strategy on DELETE-queries?

Options: ReplyQuote


Subject
Views
Written By
Posted
Index not used in certain DELETE-queries?
5809
February 21, 2009 06:23PM
1929
April 15, 2009 12:15PM
2125
April 16, 2009 04:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.