RE: Performance of delete using in
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
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
Re: Performance of delete using in
That is the entire sql statement - I didn't think I needed to list the 1500 ints that are in the in clause. Also want to mention that I ran explain on it, and it is using the index on event_id. On Wed, Apr 24, 2013 at 6:49 PM, Michael Dykman mdyk...@gmail.com wrote: You would have to show us the whole sql statement but often 'in' clauses can be refactored into equivalent joins which tend to improve performance tremendously. - michael dykman On Wed, Apr 24, 2013 at 8:19 PM, Larry Martell larry.mart...@gmail.com wrote: I have a table that has 2.5 million rows and 9 columns that are all int except for 2 varchar(255) - i.e. not that big of a table. I am executing a delete from that table like this: delete from cdsem_event_message_idx where event_id in () The in clause has around 1,500 items in it. event_id is an int, and there is an index on event_id. This statement is taking 1 hour and 5 minutes to run. There is nothing else hitting the database at that time, and the machine it's running on is 97% idle and has plenty of free memory. This seems extremely excessive to me. I would guess it's because of the in clause. Is there some better way to do a delete like this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Performance of delete using in
I changed it to delete one row at a time and it's taking 3 minutes. On Wed, Apr 24, 2013 at 6:52 PM, Larry Martell larry.mart...@gmail.com wrote: That is the entire sql statement - I didn't think I needed to list the 1500 ints that are in the in clause. Also want to mention that I ran explain on it, and it is using the index on event_id. On Wed, Apr 24, 2013 at 6:49 PM, Michael Dykman mdyk...@gmail.com wrote: You would have to show us the whole sql statement but often 'in' clauses can be refactored into equivalent joins which tend to improve performance tremendously. - michael dykman On Wed, Apr 24, 2013 at 8:19 PM, Larry Martell larry.mart...@gmail.com wrote: I have a table that has 2.5 million rows and 9 columns that are all int except for 2 varchar(255) - i.e. not that big of a table. I am executing a delete from that table like this: delete from cdsem_event_message_idx where event_id in () The in clause has around 1,500 items in it. event_id is an int, and there is an index on event_id. This statement is taking 1 hour and 5 minutes to run. There is nothing else hitting the database at that time, and the machine it's running on is 97% idle and has plenty of free memory. This seems extremely excessive to me. I would guess it's because of the in clause. Is there some better way to do a delete like this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
