Thomas Khalil Thomas Khalil - 15 days ago 5
MySQL Question

MySql slow_log query grouping

I'm trying to collect some stats on the worst performing queries on my database.
I've switched on MySQL's slow query logging and have been monitoring the slow_log table in the database.

I'm trying to get a count of unique query count per day, something like this:

Date count query
2016-11-20 15 'SELECT * FROM XYAA'
2016-11-20 196 'SELECT count(*) FROM BLAH'
2016-11-19 7 'SELECT * FROM XYAA'
2016-11-19 296 'SELECT count(*) FROM BLAH'


slow_log table structure:

CREATE TABLE `slow_log` (
`start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user_host` mediumtext NOT NULL,
`query_time` time NOT NULL,
`lock_time` time NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
`db` varchar(512) NOT NULL,
`last_insert_id` int(11) NOT NULL,
`insert_id` int(11) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`sql_text` mediumtext NOT NULL,
`thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' |

Answer

Try this:

SELECT DATE(start_time) as `date`,
       COUNT(*) as `count`,
       sql_text as query
FROM `slow_log`
GROUP BY DATE(start_time) , sql_text