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

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?

CodePudding user response：

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.

CodePudding user response：

SELECT distinct(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time > '00:00:00'

I am assuming you need to get the distinct query time, you can directly use the distinct keyword instead of the UNION. If you want to exclude the time '00:00:00', '01:00:00', '05:00:00'.. then you can add them in the NOT IN operator.

SELECT distinct(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time > '00:00:00' and query_time NOT IN ('00:00:00', '01:00:00', '05:00:00',...)