title: SQL Joins, Count(), and group by to sort 'posts' by # of yes/no 'votes'
body: <p>I have <code>posts</code>, <code>votes</code>, and <code>comments</code> tables. Each post can have N 'yes votes', N 'no votes' and N comments. I am trying to get a set of posts sorted by number of yes votes.</p>
  
  <p>I have a query that does exactly this, but is running far too slowly. On a data set of 1500 posts and 15K votes, it's take .48 seconds on my dev machine. How can I optimize this?</p>
  
  <pre><code>select
    p.*,
    v.yes,
    x.no
  from
    posts p
  left join (select post_id, vote_type_id, count(1) as yes from votes where (vote_type_id = 1) group by post_id) v on v.post_id = p.id
  left join (select post_id, vote_type_id, count(1) as no from votes where (vote_type_id = 2) group by post_id) x on x.post_id = p.id
  left join (select post_id, count(1) as comment_count from comments group by post_id) p on p.confession_id = p.id
  order by
    yes desc
  limit
      0, 10
  </code></pre>
  
  <p><strong>EDIT:</strong></p>
  
  <ul>
  <li><code>Votes</code> and <code>Comments</code> both have a <code>post_id</code> FK</li>
  <li>Adding an index on <code>vote_type_id</code> and <code>post_id</code> in the <code>votes</code> table shaved .1sec off the query execution.</li>
  </ul>