CODEWITHSUNDEEP
mysql
user2980769
user2980769

Reputation: 141

UPDATE query containing IN with subquery is very slow

I have this UPDATE query to change the statuses (status=1) of records in the videos table for all id_video which have a count of 10 records in the thumbnail table where thubmnails.status is 1.

The query works but its very slow due to the large size of the thumbnails table. Any suggestions on how I could improve the speed of this query?

UPDATE videos 
SET    videos.status = 1 
WHERE  videos.id_video IN (SELECT thumbnails.id_video 
                           FROM   thumbnails 
                           WHERE  thumbnails.status = 1 
                           GROUP  BY thumbnails.id_video 
                           HAVING Count(thumbnails.id_thumbnail) = 10) 
       AND videos.status = 2;

Indexes are set for id_video in both tables & id_thumbnail for thumbnails table. And also indexes for status columns.

Upvotes: 3

Views: 242

Answers (3)

Jonas
Jonas

Reputation: 784

I removed the IN clause by making your query more simple

UPDATE videos v
SET v.status = 1
WHERE v.status = 2 AND (
    SELECT COUNT(t.id_thumbnail)
    FROM thumbnails t
    WHERE t.id_video = v.id_video AND t.status = 1
) = 10;

Videos v records only get updated when they have status 2 and than they have a count of 10 thumbnails t with the video id of v and status 1.

Upvotes: 2

kjmerf
kjmerf

Reputation: 4335

You could also try using EXISTS instead of IN:

UPDATE videos 
SET    videos.status = 1 
WHERE  videos.status = 2
AND EXISTS (SELECT thumbnails.id_video 
            FROM   thumbnails 
            WHERE  thumbnails.status = 1 
            GROUP  BY thumbnails.id_video 
            HAVING Count(thumbnails.id_thumbnail) = 10
            AND thumbnails.id_video = videos.id_video);

Upvotes: 0

Rahul
Rahul

Reputation: 77866

What if you convert that IN clause to a JOIN condition like

UPDATE videos v
JOIN (SELECT thumbnails.id_video 
                           FROM   thumbnails 
                           WHERE  thumbnails.status = 1 
                           GROUP  BY thumbnails.id_video 
                           HAVING Count(thumbnails.id_thumbnail) = 10) xxx
ON v.id_video = xxx.id_video
SET v.status = 1 
WHERE v.status = 2;

Upvotes: 0

Related Questions