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
