Search code examples
sqlmysqlaggregate-functions

Can this MYSQL query be more efficient in counting table entries by group

I am currently using this clunky query to count entries in the slow_log by time groups:

SELECT count(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time > '00:59:00'
UNION
SELECT count(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time < '01:00:00' and query_time > '00:50:00'
UNION
SELECT count(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time < '00:50:00' and query_time > '00:40:00'
UNION
SELECT count(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time < '00:40:00' and query_time > '00:30:00'
UNION
SELECT count(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time < '00:30:00' and query_time > '00:20:00'
UNION
SELECT count(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time < '00:20:00' and query_time > '00:10:00'
UNION
SELECT count(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time < '00:10:00' and query_time > '00:5:00'
UNION
SELECT count(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time < '00:5:00' and query_time > '00:02:00'
UNION
SELECT count(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time < '00:02:00' and query_time > '00:01:00'
UNION
SELECT count(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time < '00:01:00' and query_time > '00:00:00'

IS there a better more efficient way to do this?

Solution

  • You can use case expression to assign labels to counts and group by them:

    SELECT CASE
    -- cases must be sorted descending
    WHEN query_time > '01:00:00' THEN '> 01:00:00'
    WHEN query_time > '00:50:00' THEN '> 00:50:00'
    -- other ranges in between
    WHEN query_time > '00:01:00' THEN '> 00:01:00'
    ELSE                              '<= one minute'
END AS `label`, COUNT(*) AS `count`
FROM `slow_log`
WHERE `db` LIKE 'taco_query'
GROUP BY 1

    Note that your original query skips exact values (e.g. 00:50:00.000 won't match any where clause). This one will put it in > 00:40:00 bracket. I'd rather use >= in brackets.