List:General Discussion« Previous MessageNext Message »
From:Rick James Date:April 30 2013 11:00pm
Subject:RE: Performance of delete using in
View as plain text  
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:dj@stripped]
> 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

Thread
Performance of delete using inLarry Martell25 Apr
  • Re: Performance of delete using inDenis Jedig25 Apr
    • RE: Performance of delete using inRick James30 Apr
Re: Performance of delete using inLarry Martell25 Apr
  • Re: Performance of delete using inLarry Martell25 Apr