mysql group by year_MySQL文件在GROUP BY YEAR和Month上

我有一个大表,存储我的Web应用程序的调试信息.问题是该表现在是500,000行,其中一个查询很慢,因为没有使用索引.

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;

结果：

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

这是表格结构.

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`)

)

如果有人有任何想法获得相同的结果没有文件排序,这将是非常棒的！

解决方法:

GROUP BY意味着MySQL中的ORDER BY

因此,尝试添加ORDER BY NULL：这通常会消除文件排序

标签：mysql,indexing,group-by

来源： https://codeday.me/bug/20190621/1250950.html