MYSQL fetch 10 posts, each w/ vote count, sorted by vote count, limited by where clause on posts
- by nibblebot
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