这个MYSQL查询在按组统计表项时是否更有效

user389391 • 3 年前 • 1321 次点击

我目前正在使用这个笨重的查询按时间组计算slow_日志中的条目:

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'

有没有更好更有效的方法?

Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/129099

文章 [ 2 ] | 最新文章 3 年前

• 1 楼

Ramesh RV 3 年前

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

我假设您需要获得distinct查询时间,您可以直接使用distinct关键字而不是UNION。如果要排除时间“00:00:00”、“01:00:00”、“05:00:00”。。然后可以将它们添加到NOT in操作符中。

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',...)

• 2 楼

Salman A 3 年前

可以使用大小写表达式将标签指定给计数并按其分组:

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

请注意,原始查询跳过精确值(例如,00:50:00.000将不匹配任何where子句)。这个可以放进去 > 00:40:00 支架我宁愿使用 >= 括号内。

登录后回复