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:dj@stripped]
> Sent: Wednesday, April 24, 2013 10:50 PM
> To: firstname.lastname@example.org
> Subject: Re: Performance of delete using in
> 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
> Kind regards,
> Denis Jedig
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql