List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:April 25 2013 1:31am
Subject:Re: Performance of delete using in
View as plain text  
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.martell@stripped> 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 <mdykman@stripped> 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.martell@stripped>
>> 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
>>  - mdykman@stripped
>>
>>  May the Source be with you.
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