Re: Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with multiple a column key?
As I know, IN sometimes invoke unmormal index. On Thu, May 28, 2009 at 1:15 AM, Baron Schwartz ba...@xaprb.com wrote: Simon, On Wed, May 27, 2009 at 11:23 AM, Simon J Mudd sjm...@pobox.com wrote: per...@elem.com (Perrin Harkins) writes: On Wed, May 27, 2009 at 6:43 AM, Simon J Mudd sjm...@pobox.com wrote: 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? Not according to the docs: http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in I'm not sure that the reference makes anything clear. The statements are wrote ARE valid SQL and even though containing mulitiple column values ARE constants. Problem is I'm finding it hard to find a definitive reference to something like this. I'll have to check my Joe Celko books to see if he mentions ths. Nothing's wrong with the SQL -- it's just that MySQL doesn't optimize this type of query well. See http://code.openark.org/blog/mysql/mysql-not-being-able-to-utilize-a-compound-index Regards Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn Comanpy: http://www.actionsky.com
Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with multiple a column key?
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 '-00-00 00:00:00', `last_timestamp` datetime NOT NULL default '-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 | +-++-+-+ | 10 | 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 | | 10 | 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 | | 10 | 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 | | 10 | 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', 10 ), ( 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', 10 ) 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
Re: Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with multiple a column key?
On Wed, May 27, 2009 at 6:43 AM, Simon J Mudd sjm...@pobox.com wrote: 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? Not according to the docs: http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with multiple a column key?
per...@elem.com (Perrin Harkins) writes: On Wed, May 27, 2009 at 6:43 AM, Simon J Mudd sjm...@pobox.com wrote: 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? Not according to the docs: http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in I'm not sure that the reference makes anything clear. The statements are wrote ARE valid SQL and even though containing mulitiple column values ARE constants. Problem is I'm finding it hard to find a definitive reference to something like this. I'll have to check my Joe Celko books to see if he mentions ths. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with multiple a column key?
Simon, On Wed, May 27, 2009 at 11:23 AM, Simon J Mudd sjm...@pobox.com wrote: per...@elem.com (Perrin Harkins) writes: On Wed, May 27, 2009 at 6:43 AM, Simon J Mudd sjm...@pobox.com wrote: 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? Not according to the docs: http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in I'm not sure that the reference makes anything clear. The statements are wrote ARE valid SQL and even though containing mulitiple column values ARE constants. Problem is I'm finding it hard to find a definitive reference to something like this. I'll have to check my Joe Celko books to see if he mentions ths. Nothing's wrong with the SQL -- it's just that MySQL doesn't optimize this type of query well. See http://code.openark.org/blog/mysql/mysql-not-being-able-to-utilize-a-compound-index Regards Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
