I want to fetch a set of Posts w/ vote count listed, sorted by vote count (e.g.)
Post 1 - Post Body blah blah - Votes: 500
Post 2 - Post Body blah blah - Votes: 400
Post 3 - Post Body blah blah - Votes: 300
Post 4 - Post Body blah blah - Votes: 200
I have 2 tables:
Posts - columns - id, body, is_hidden
Votes - columns - id, post_id, vote_type_id
Here is the query I've tried:
SELECT p.*, v.yes_count
FROM posts p
LEFT JOIN
(SELECT post_id, vote_type_id, COUNT(1) AS yes_count
FROM votes
WHERE (vote_type_id = 1)
GROUP BY post_id
ORDER BY yes_count DESC
LIMIT 0, 10) v
ON v.post_id = p.id
WHERE (p.is_hidden = 0)
ORDER BY yes_count DESC
LIMIT 0, 10
Correctness: The above query almost works. The subselect is including votes for posts that have is_hidden = 1, so when I left join it to posts, if a hidden post is in the top 10 (ranked by votes), I can end up with records with NULL on the yes_count field.
Performance: I have ~50k posts and ~500k votes. On my dev machine, the above query is running in .4sec. I'd like to stay at or below this execution time.
Indexes: I have an index on the Votes table that covers the fields: vote_type_id and post_id
EXPLAIN
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY p ALL NULL NULL NULL NULL 45985 Using where; Using temporary; Using filesort
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10
2 DERIVED votes ref VotingPost VotingPost 4 319881 Using where; Using index; Using temporary; Using filesort
