From: Larry Martell (larry.martellgmail.com)
Date: Wed Apr 24 2013 - 19:52:21 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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 <mdykmangmail.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.martellgmail.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
> - mdykmangmail.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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]