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/mysql?unsub=arch...@jab.org