Question

目前,我用下面的问询进行 j。在可能的情况下,你不知道jsPerf,有两个表格:pages。载有测试案例/修订和<代码>测试,其中载有测试案件中测试所用的密码。

目前有937份记录载于<编码>网页,3817份记录载于tests/code>。



如您所见,在使用时使用该查询。


大约需要7秒才能执行:

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


I've Additional Index on all areas that listed in  WHERE clauses. 我是否应当增加?

如何优化这一询问?

P.S.,我知道,我可以实施PHP的套间系统——我很可能不会告诉我:)我真的希望找到如何改进这一询问。

Answer 1

使用:

   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

Answer 2

你们要学习如何使用EXPLAIN。这将执行l状,并显示正在使用指数和正在做什么浏览扫描。目标是减少浏览扫描次数(即数据库逐行检索价值)。

Answer 3

你们可能想在看到哪一个排最慢的时候尝试一个子座。

问题:

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

.解结果。这可能是缓慢的。

友情链接