CODEWITHSUNDEEP
mysqlsqldatabaseoptimizationcount
Future King
Future King

Reputation: 3849

How to optimize count and group by subquery operation

My host is saying that the following query is taking lots of Server CPU. Please tell me how can I optimize it.

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

Please note that the analytics table contains the records for all the pageviews and clicks. So it is very very large.

Upvotes: 0

Views: 65

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

This is your query:

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

The subquery requires extra effort as does the group by. You can replace this with:

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';

You might then be able to do further optimizations using indexes, but it would be helpful to see an explain of this query and the current indexes on the tables.

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26363

As far as I can tell, your query just counts distinct wall_id values after filtering via the joins and the WHERE clause. Something like this should be close:

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'

Upvotes: 1

Related Questions