Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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?

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>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.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading