List:General Discussion« Previous MessageNext Message »
From:Simon J Mudd Date:May 27 2009 10:43am
Subject:Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with multiple a column key?
View as plain text  
This is in 5.0.68 and 5.1.34.

I'm trying to cleanup some old data in a table which looks like the following:

CREATE TABLE `transaction_history` (
 `customer_id` int(10) unsigned NOT NULL default '0',
 `transaction_id` int(10) unsigned NOT NULL default '0',
 `first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
 `last_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
 PRIMARY KEY  (`transaction_id`,`first_timestamp`,`customer_id`),
 KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> SELECT * FROM transaction_history LIMIT 10;
+-------------+----------------+---------------------+---------------------+
| customer_id | transaction_id | first_timestamp     | last_timestamp      |
+-------------+----------------+---------------------+---------------------+
|      100000 |       31536827 | 2009-01-22 13:25:26 | 2009-01-22 13:40:21 |
|      306636 |       31536827 | 2009-01-22 13:43:56 | 2009-01-22 13:44:02 |
|      100000 |       31536827 | 2009-01-22 13:50:24 | 2009-01-22 13:50:46 |
|      306636 |       31536827 | 2009-01-22 13:50:53 | 2009-01-22 13:59:13 |
|      304142 |       31536827 | 2009-01-22 14:53:00 | 2009-01-22 14:53:00 |
|      306636 |       31536827 | 2009-01-22 15:03:59 | 2009-01-22 15:03:59 |
|      100000 |       31536827 | 2009-01-22 15:06:15 | 2009-01-22 15:09:01 |
|      306636 |       31536827 | 2009-01-22 15:09:41 | 2009-01-22 15:10:32 |
|      100000 |       31536827 | 2009-01-22 15:10:42 | 2009-01-22 15:19:48 |
|      306636 |       31536827 | 2009-01-22 15:30:41 | 2009-01-22 16:01:28 |
+-------------+----------------+---------------------+---------------------+
10 rows IN set (0.02 sec)

I need to identify the rows to be deleted and was planning on doing something like:

mysql> EXPLAIN SELECT * FROM transaction_history WHERE
> (`transaction_id`,`first_timestamp`,`customer_id`) IN ( ( 31536827, '2009-01-22 13:25:26',
> 100000 ), ( 31536827, '2009-01-22 13:43:56', 306636 ) );
+----+-------------+-----------------------+------+---------------+------+---------+------+----------+-------------+
| id | SELECT_type | table                 | type | possible_keys | key  | key_len | ref 
| rows     | Extra       |
+----+-------------+-----------------------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | transaction_history   | ALL  | NULL          | NULL | NULL    | NULL
| 73181118 | Using WHERE |
+----+-------------+-----------------------+------+---------------+------+---------+------+----------+-------------+
1 row IN set (0.00 sec)

As you can see MySQL is ignoring or not recognising the primary key
"definition" in the where clause and thus planning on doing a table scan.

The simple approach is recognised correctly:

mysql> EXPLAIN SELECT * FROM transaction_history WHERE
> (`transaction_id`,`first_timestamp`,`customer_id`) = ( 31536827, '2009-01-22 13:25:26',
> 100000 ) OR (`transaction_id`,`first_timestamp`,`customer_id`) = ( 31536827, '2009-01-22
> 13:43:56', 306636 );
+----+-------------+-----------------------+-------+----------------------+---------+---------+------+------+-------------+
| id | SELECT_type | table                 | type  | possible_keys        | key     |
key_len | ref  | rows | Extra       |
+----+-------------+-----------------------+-------+----------------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | transaction_history   | range | PRIMARY,customer_id  | PRIMARY | 16  
   | NULL |    2 | Using WHERE |
+----+-------------+-----------------------+-------+----------------------+---------+---------+------+------+-------------+
1 row IN set (0.02 sec)

So is the format of the DELETE FROM .. WHERE ... IN ( ... )  clause I
propose valid and SHOULD the optimiser recognise this and be expected
to just find the 2 rows by searching on the primary key?

Simon
Thread
Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with multiple a column key?Simon J Mudd27 May
  • Re: Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with multiple a column key?Perrin Harkins27 May
    • Re: Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with multiple a column key?Simon J Mudd27 May
      • Re: Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with multiple a column key?Baron Schwartz27 May
        • Re: Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with multiple a column key?Moon's Father31 May