title: How to optimize this low-performance MySQL query?
body: <p>I’m currently using the following query for jsPerf. In the likely case you don’t know jsPerf — there are two tables: <code>pages</code> containing the test cases / revisions, and <code>tests</code> containing the code snippets for the tests inside the test cases.</p>
  
  <p>There are currently 937 records in <code>pages</code> and 3817 records in <code>tests</code>.</p>
  
  <p>As you can see, it takes quite a while to load <a href="http://jsperf.com/browse" rel="nofollow noreferrer">the “Browse jsPerf” page</a> where this query is used.</p>
  
  <p>The query takes about 7 seconds to execute:</p>
  
  <pre><code>SELECT
   id AS pID,
   slug AS url,
   revision,
   title,
   published,
   updated,
   (
    SELECT COUNT(*)
    FROM pages
    WHERE slug = url
    AND visible = "y"
   ) AS revisionCount,
   (
    SELECT COUNT(*)
    FROM tests
    WHERE pageID = pID
   ) AS testCount
   FROM pages
   WHERE updated IN (
    SELECT MAX(updated)
    FROM pages
    WHERE visible = "y"
    GROUP BY slug
   )
   AND visible = "y"
   ORDER BY updated DESC
  </code></pre>
  
  <p>I’ve added indexes on all fields that appear in <code>WHERE</code> clauses. Should I add more?</p>
  
  <p>How can this query be optimized?</p>
  
  <p>P.S. I know I could implement a caching system in PHP — I probably will, so please don’t tell me :) I’d just really like to find out how this query could be improved, too.</p>