MySQL filesort in GROUP BY YEAR & Month

Question

I have a large table that stores debug data for my web application. The problem is that the table now has 500,000 rows, and one of the queries is slow because the index is not used.

SQL:

EXPLAIN SELECT count(*) AS `count`, month(event_date) AS `month`, year(event_date) AS `year`FROM events WHERE 1 = 1 GROUP BY year(event_date) DESC, month(event_date) DESC LIMIT 6; 

Result:

 SIMPLE events index NULL event_date 8 NULL 139358 Using index; Using temporary; Using file sort 

And here is the table structure.

 CREATE TABLE IF NOT EXISTS `events` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Event Primary Key', `event_number` int(11) NOT NULL, `user_id` int(11) unsigned NOT NULL COMMENT 'User ID', `server_id` int(11) unsigned DEFAULT NULL COMMENT 'The ID of the remote log client', `remote_id` int(11) unsigned DEFAULT NULL COMMENT 'The Event Primary Key from the remote client', `event_date` datetime NOT NULL COMMENT 'Event Datetime in local timezone', `event_date_utc` datetime NOT NULL COMMENT 'Event Datetime in UTC timezone', `event_type` varchar(255) NOT NULL COMMENT 'The type of event', `event_source` varchar(255) NOT NULL COMMENT 'Text description of the source of the event', `event_severity` varchar(255) NOT NULL COMMENT 'Notice, Warning etc', `event_file` text NOT NULL COMMENT 'The full file location of the source of the event', `event_file_line` int(11) NOT NULL COMMENT 'The line in the file that triggered the event', `event_ip_address` varchar(255) NOT NULL COMMENT 'IP Address of the user that triggered the event', `event_summary` varchar(255) NOT NULL COMMENT 'A summary of the description', `event_description` text NOT NULL COMMENT 'Full description of the event', `event_trace` text NOT NULL COMMENT 'Full PHP trace', `event_synced` int(1) unsigned DEFAULT '0', PRIMARY KEY (`id`), KEY `event_type` (`event_type`), KEY `event_source` (`event_source`), KEY `user_id` (`user_id`), KEY `server_id` (`server_id`), KEY `event_date` (`event_date`) ) 

If someone has ideas on getting the same results without sorting the files, that would be great!

Answer 1

GROUP BY implies ORDER BY in MySQL

So try adding ORDER BY NULL: this usually excludes filesort file

See "ORDER BY Optimization" in MySQL docs

Answer 2

Your key problem is that you are specifying a WHERE . Your use of WHERE 1=1 pointless. The problem is that you are trying to get YEAR and MONTH from MySQL without limiting the number of rows, and therefore it processes MONTH (..) and YEAR (...) for each row before it can process the group.

The fact that he still does not use INDEX after my previous sentence indicates that you have more to your request than you allow if this case tells me, and I can help you. Otherwise, I would recommend checking below (although I need to guess your goals, since you did not indicate what you are trying to achieve)

If you use the last 6 calendar months, this will also help greatly.

 SELECT COUNT(id) AS `count`, MONTH(event_date) AS `month`, YEAR(event_date) AS `year` FROM events -- Get the first day of this month, and subtract 6 months WHERE event_date > SUBDATE(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 6 MONTH) GROUP BY `year` DESC, `month` DESC; 

If you have additional WHERE criteria, this will change the advice given, so if this happens, update

Answer 3

In addition to what others have posted:

If you run EXPLAIN SELECT... and MySQL reports that it does not use the index for this query (or not the one you want), you can solve this by querying the data with SELECT... FORCE INDEX... For more information on the syntax of this, see here: http://dev.mysql.com/doc/refman/5.6/en/index-hints.html

Answer 4

• You use SELECT *, which means selecting all rows, so the whole table is scanned - try selecting specific rows to show

• There are also no parameters for filtering data, so the whole table will be read and returned, try limiting either by date or by another parameter