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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
