mysql> show create table headlines_dataflow\G;
*************************** 1. row ***************************
Table: headlines_dataflow
Create Table: CREATE TABLE `headlines_dataflow` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`headline_type` tinyint(3) unsigned NOT NULL COMMENT '云计算头条|大数据头条',
`article_id` int(11) unsigned NOT NULL COMMENT '推荐的文章 id',
`data_type` tinyint(3) unsigned NOT NULL,
`recommend_uid` bigint(20) unsigned NOT NULL COMMENT '推荐人',
`title` varchar(255) NOT NULL DEFAULT '' COMMENT '标题',
`img` varchar(255) NOT NULL DEFAULT '' COMMENT '封面图',
`abstract` text NOT NULL COMMENT '简介',
`create_month` mediumint(6) unsigned NOT NULL COMMENT '推荐的月份,需要作为检索条件',
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `data_id` (`article_id`,`headline_type`),
KEY `headline_type` (`headline_type`,`data_type`),
KEY `create_month` (`create_month`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
1 row in set (0.03 sec)
ERROR:
No query specified
mysql> explain select distinct create_month from headlines_dataflow where headline_type=2;
+----+-------------+--------------------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+-------+---------------+--------------+---------+------+------+-------------+
| 1 | SIMPLE | headlines_dataflow | index | headline_type | create_month | 3 | NULL | 5 | Using where |
+----+-------------+--------------------+-------+---------------+--------------+---------+------+------+-------------+
1 row in set (0.04 sec)
mysql> show create table headlines_dataflow\G;
*************************** 1. row ***************************
Table: headlines_dataflow
Create Table: CREATE TABLE `headlines_dataflow` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`headline_type` tinyint(3) unsigned NOT NULL COMMENT '云计算头条|大数据头条',
`article_id` int(11) unsigned NOT NULL COMMENT '推荐的文章 id',
`data_type` tinyint(3) unsigned NOT NULL,
`recommend_uid` bigint(20) unsigned NOT NULL COMMENT '推荐人',
`title` varchar(255) NOT NULL DEFAULT '' COMMENT '标题',
`img` varchar(255) NOT NULL DEFAULT '' COMMENT '封面图',
`abstract` text NOT NULL COMMENT '简介',
`create_month` mediumint(6) unsigned NOT NULL COMMENT '推荐的月份,需要作为检索条件',
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `data_id` (`article_id`,`headline_type`),
KEY `headline_type` (`headline_type`,`data_type`),
KEY `headline_type_2` (`headline_type`,`create_month`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
1 row in set (0.04 sec)
ERROR:
No query specified
mysql> explain select distinct create_month from headlines_dataflow where headline_type=2;
+----+-------------+--------------------+------+-------------------------------+-----------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+------+-------------------------------+-----------------+---------+-------+------+--------------------------+
| 1 | SIMPLE | headlines_dataflow | ref | headline_type,headline_type_2 | headline_type_2 | 1 | const | 5 | Using where; Using index |
+----+-------------+--------------------+------+-------------------------------+-----------------+---------+-------+------+--------------------------+
1 row in set (0.03 sec)