MySQL Forums :: InnoDB :: Index not used in certain DELETE-queries?


Advanced Search

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? 5699 Maarten van Schaik 02/21/2009 06:23PM
Re: Index not used in certain DELETE-queries? 2243 Rick James 02/22/2009 12:10AM
Re: Index not used in certain DELETE-queries? 2065 Nanda kishore Toomula 02/23/2009 02:30AM
Re: Index not used in certain DELETE-queries? 1996 Maarten van Schaik 02/23/2009 03:27AM
Re: Index not used in certain DELETE-queries? 1976 aftab khan 02/23/2009 08:51AM
Re: Index not used in certain DELETE-queries? 2051 aftab khan 02/23/2009 08:53AM
Re: Index not used in certain DELETE-queries? 2681 Maarten van Schaik 02/23/2009 10:07AM
Re: Index not used in certain DELETE-queries? 2086 Maarten van Schaik 02/23/2009 10:18AM
Re: Index not used in certain DELETE-queries? 1971 aftab khan 02/24/2009 05:16AM
Re: Index not used in certain DELETE-queries? 2042 Maarten van Schaik 02/24/2009 09:15AM
Re: Index not used in certain DELETE-queries? 2110 aftab khan 02/25/2009 07:17AM
Re: Index not used in certain DELETE-queries? 2029 Maarten van Schaik 02/26/2009 03:39AM
log sequence number 1910 vijinarayanan viswanathan 04/15/2009 12:15PM
Re: log sequence number 2103 Aftab Khan 04/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.