Slow MySQL Query: Is there a way to avoid doing a conditional select count for each row with a left join?

Question

Tables
videos: id
ratings: id, video_id, user_id, rating

There can be multiple ratings per video. The videos table has 10,000+ rows. Some videos may not have any rating data associated with them yet.

I want to pick all videos where a specific user (user_id 1 in this case) has not yet rated.

Query:

SELECT 
    videos.id, 
    (SELECT count(id) FROM ratings WHERE user_id = 1 AND ratings.video_id = videos.id) 
    AS rating_count    
FROM videos   
LEFT JOIN ratings ON ratings.video_id = videos.id  
GROUP BY videos.id  
HAVING rating_count = 0  
ORDER BY ratings.rating DESC   
LIMIT 20

Query is taking at least 4 seconds every time. Am I perhaps not supposed to use a left join here?

Answer 1

You can remove your subquery and rewrite your query as below,also you can use the user id AND ratings.user_id = 1 condition in your on clause of join so still all videos will be returned even if they are not related to that user

SELECT 
    videos.id, 
    count(ratings.id)  AS rating_count    
FROM videos   
LEFT JOIN ratings 
ON (ratings.video_id = videos.id   AND ratings.user_id = 1)
GROUP BY videos.id  
HAVING rating_count = 0  
ORDER BY ratings.rating DESC   
LIMIT 20