如何优化这个低性能的MySQL查询?

用途:

   SELECT x.id AS pID,
          x.slug AS url,
          x.revision,
          x.title,
          x.published,
          x.updated,
          y.revisionCount,
          COALESCE(z.testCount, 0) AS testCount
     FROM pages x
     JOIN (SELECT p.slug,
                  MAX(p.updated) AS max_updated,
                  COUNT(*) AS revisionCount
             FROM pages p
            WHERE p.visible = 'y'
         GROUP BY p.slug) y ON y.slug = x.slug
                           AND y.max_updated = x.updated
LEFT JOIN (SELECT t.pageid,
                  COUNT(*) AS testCount
             FROM tests t
         GROUP BY t.pageid) z ON z.pageid = x.id
 ORDER BY updated DESC

你想学习如何使用解释。这将执行sql语句,并显示正在使用哪些索引以及正在执行哪些行扫描。目标是减少行扫描的数量(即,数据库逐行搜索值)。

您可能需要一次一个子查询来查看哪个子查询最慢。

SELECT MAX(updated)
  FROM pages
  WHERE visible = "y"
  GROUP BY slug