title: Speed up MYSQL INNER JOIN Query Across Two Tables
body: <p>The purpose of this query is to act like a search. Unfortunately the query is currently running from 1.5 to 2 seconds which is unacceptable. After running EXPLAIN on the query I see it's using <i>"Using temporary; Using filesort"</i> and no indexes. However, I'm not exactly sure where you could put an index on this query.</p>
  
  <p>The ORDER BY also slows the query down a whole lot as well but it's needed.
  Any suggestions on how to improve this query?</p>
  
  <pre><code>SELECT DISTINCT p.id, p.date,
  (
    SELECT COUNT(post_id) FROM post_tags WHERE post_id = pt.post_id
  ) as t_count 
  FROM post_tags pt 
  INNER JOIN posts p 
    ON (pt.post_id = p.id) 
  WHERE pt.t_id IN (7,456)
  ORDER BY t_count, p.s_count DESC, p.id DESC 
  LIMIT 0, 50;
  </code></pre>
  
  <p>Here is the EXPAIN statement: <a href="https://gist.github.com/e742982e435cf082c033" rel="nofollow">https://gist.github.com/e742982e435cf082c033</a></p>