List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:April 25 2013 12:52am
Subject:Re: Performance of delete using in
View as plain text  
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