求助，mysql统计实时数据信息的，查询速度很慢？

[云天河V]

CREATE TABLE `action_log` (
`pkid` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键Id',
`enterprise_id` int(11) unsigned NOT NULL COMMENT '企业ACCOUNTID',
`student_id` int(11) unsigned NOT NULL COMMENT '学生ACCOUNTID',
`action_id` tinyint(1) NOT NULL DEFAULT '0' COMMENT '操作ID',
`content` varchar(255) NOT NULL DEFAULT '0' COMMENT '操作内容',
`create_time` datetime NOT NULL COMMENT '记录时间',
`scence` tinyint(1) DEFAULT NULL COMMENT '行为场景,1:pc,2:wap,3:weixin,4:ios,5:android',
PRIMARY KEY (`pkid`),
KEY `student_id` (`student_id`) USING BTREE
KEY `enterprise_id` (`enterprise_id`) USING BTREE
KEY `action_id` (`action_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=22579 DEFAULT CHARSET=utf8 COMMENT='行为日志表';


SQL: SELECT COUNT(pkid) AS tp_count FROM `action_log` WHERE ( `create_time` >= '2016-08-29 00:00:00' AND `create_time` <= '2016-08-29 09:33:06' ) AND `student_id` <> '' AND `enterprise_id` <> '' AND `action_id` = 2 LIMIT 1

[ RunTime:3.7178s ]
60万数据量，将近4秒

[一起混吧]

贴出 explain ... 的结果看看

[ayzen1988]

student_id、enterprise_id用的是int类型，可以用0当默认值
create_time加上索引

SELECT COUNT(pkid) AS tp_count FROM `action_log` WHERE ( `create_time` >= '2016-08-29 00:00:00' AND `create_time` <= '2016-08-29 09:33:06'  ) AND `student_id` > 0 AND `enterprise_id` > 0 AND `action_id` = 2 LIMIT 1

[果酱很好吃]

create_time 加个索引

[云天河V]

引用 2 楼 zhangbin1988 的回复:

student_id、enterprise_id用的是int类型，可以用0当默认值
create_time加上索引

SELECT COUNT(pkid) AS tp_count FROM `action_log` WHERE ( `create_time` >= '2016-08-29 00:00:00' AND `create_time` <= '2016-08-29 09:33:06'  ) AND `student_id` > 0 AND `enterprise_id` > 0 AND `action_id` = 2 LIMIT 1

嗯，确实时间降下来啦，谢谢

[云天河V]

引用 2 楼 zhangbin1988 的回复:

student_id、enterprise_id用的是int类型，可以用0当默认值
create_time加上索引

SELECT COUNT(pkid) AS tp_count FROM `action_log` WHERE ( `create_time` >= '2016-08-29 00:00:00' AND `create_time` <= '2016-08-29 09:33:06'  ) AND `student_id` > 0 AND `enterprise_id` > 0 AND `action_id` = 2 LIMIT 1

这个student_id>0的索引没有起作用，如果不加create_time的话，在1.5秒左右