Please provide SHOW CREATE TABLE cdsem_event_message_idx \G EXPLAIN SELECT * FROM cdsem_event_message_idx where event_id in (....) \G SHOW VARIABLES LIKE 'autocommit';
These can impact DELETE speed: * secondary indexes * whether event_id is indexed. * disk type and speed -- ordinary SATA vs RAID vs SSD vs ... * ENGINE -- SHOW CREATE will provide that info * MySQL version -- perhaps IN optimization has improved over time Rule of Thumb: 100 iops. Hence 1500 deletes is likely to take 15 seconds if they are randomly place, no secondary keys, and on non-RAIDed SATA drive. DELETEing one row at a time incurs network and parsing overhead, so it is not surprising that it is slower. That seems like a lot of overhead, so I would guess you are using InnoDB and have most of autocommit=1 and sync_binlog=1 and innodb_flush_log_at_trx_commit=1 > -----Original Message----- > From: Denis Jedig [mailto:d...@syneticon.net] > Sent: Wednesday, April 24, 2013 10:50 PM > To: mysql@lists.mysql.com > Subject: Re: Performance of delete using in > > Larry, > > Am 25.04.2013 02:19, schrieb Larry Martell: > > > delete from cdsem_event_message_idx where event_id in (....) > > > > The in clause has around 1,500 items in it. > > Consider creating a temporary table, filling it with your "IN" > values and joining it to cdsem_event_message_idx ON event_id for > deletion. > > Kind regards, > > Denis Jedig > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
