CODEWITHSUNDEEP
mysql
user2980769
user2980769

Reputation: 141

Slow subquery with in clause

I have a query to select videos from a database.

Each video has multiple thumbnails associated with it from the thumbnails table. I want to avoid including any videos in results which have thumbnail(s) status of 0, 2, 3. I wasn't able to just join the thumbnails table because each video has multiple related thumbnails with varying statuses (for each thumbnail).

This query is quite slow, are there any possible ways I could optimize it? Or is there a way to do what I need without a subquery.

SELECT `videos`.`id_video`, `videos`.`date_added`
FROM `videos`
WHERE (`videos`.`status` = 1 AND NOT (`videos`.`id_video` IN (SELECT U1.`id_video` AS Col1 FROM `thumbnails` U1 WHERE U1.`status` IN (0, 2, 3))))
LIMIT 5000;

Upvotes: 1

Views: 32

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169274

Give that a shot:

SELECT `videos`.`id_video`, `videos`.`date_added`
FROM `videos`
WHERE `videos`.`status` = 1 AND 
    NOT EXISTS (
        SELECT 1
        FROM `thumbnails` U1 
        WHERE U1.`status` IN (0, 2, 3) AND 
        `videos`.`id_video` = U1.`id_video` 
    )
LIMIT 5000;

Upvotes: 2

Related Questions