CODEWITHSUNDEEP
mysql
devinpleuler
devinpleuler

Reputation: 163

Optimizing a MySQL Subselect Update

I'm having a hard time getting this update to run in a reasonable amount of time. Notice - events_copy is a copy of events since I couldn't update the table I was querying one.

update events 
    set dummy = 1 
    where event_id in 
    (select event_id 
        from events_copy 
        join qualifiers using (event_id) 
        where type = 10);

I thought this might be an alternative as a "create table x from ...", but the group by takes way too long.

select 
    events.*, 
    if(type = 10, 1, 0) 
    from events 
    left join qualifiers using (event_id) 
    group by event_id;

Upvotes: 0

Views: 57

Answers (1)

Zane Bien
Zane Bien

Reputation: 23125

Use a JOIN in your update rather than subquery:

UPDATE events a
INNER JOIN
(
    SELECT DISTINCT event_id
    FROM events_copy
    INNER JOIN qualifiers USING (event_id)
    WHERE type = 10
) b ON a.event_id = b.event_id
SET a.dummy = 1

The reason why it's slow is because the subquery is executing and performing a join for each row in your events table. Instead, the subselect will execute once.

Upvotes: 1

Related Questions