mysql - 如何优化count和group by子查询操作

Question

我的主机说以下查询占用了大量服务器 CPU。请告诉我如何优化它。

SELECT COUNT(*) FROM (SELECT COUNT(*) AS tot,wallpapers.*,resolutions.res_height,resolutions.res_width FROM wallpapers

INNER JOIN analytics ON analytics.`wall_id` = wallpapers.`wall_id`

INNER JOIN resolutions ON resolutions.`res_id` = wallpapers.`res_id`



WHERE analytics.ana_date >= '2013-09-01 16:36:56' AND wallpapers.wall_status = 'public'

GROUP BY analytics.`wall_id`) as Q 

请注意，分析表包含所有浏览量和点击量的记录。所以它非常非常大。

Answer 1

据我所知，您的查询只是在通过联接和 WHERE 子句进行过滤后计算不同的 wall_id 值。像这样的东西应该很接近:

SELECT COUNT(DISTINCT analytics.wall_id)
FROM wallpapers
INNER JOIN analytics ON analytics.wall_id = wallpapers.wall_id
INNER JOIN resolutions ON resolutions.res_id = wallpapers.res_id
WHERE analytics.ana_date >= '2013-09-01 16:36:56'
  AND wallpapers.wall_status = 'public'