56,681
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE `data_log` (
`uniqueid` varchar(20) NOT NULL,
`cam_id` varchar(8) default NULL,
`call_date` datetime default NULL,
`status` varchar(6) default NULL,
PRIMARY KEY (`uniqueid`),
KEY `call_date` (`call_date`),
KEY `cam_id` (`cam_id`),
KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
insert into data_log values
('100001','cam1','2012-11-14 08:19:40','A'),('100002','cam1','2012-11-14 08:19:40','B'),('100003','cam1','2012-11-15 08:19:40','C')
,('100004','cam1','2012-11-15 08:19:40','D'),('100005','cam2','2012-11-14 08:19:40','A'),('100006','cam2','2012-11-14 08:19:40','A')
,('100007','cam2','2012-11-15 08:19:40','B'),('100008','cam2','2012-11-15 08:19:40','C'),('100009','cam1','2012-11-14 08:19:40','A'),
('100010','cam1','2012-11-15 08:19:40','B'),('100011','cam2','2012-11-15 08:19:40','A'),('100012','cam2','2012-11-14 08:19:40','C'),
('100013','cam1','2012-11-15 08:19:40','A'),('100014','cam2','2012-11-15 08:19:40','D'),('100015','cam1','2012-11-14 08:19:40','D'),
('100016','cam3','2012-11-14 08:19:40','A'),('100017','cam3','2012-11-14 08:19:40','D'),('100018','cam3','2012-11-15 08:19:40','D'),
('100019','cam3','2012-11-15 08:19:40','C'),('100020','cam3','2012-11-14 08:19:40','A'),('100021','cam2','2012-11-14 08:19:40','B'),
('100022','cam3','2012-11-15 08:19:40','B'),('100023','cam1','2012-11-14 08:19:40','C'),('100024','cam4','2012-11-14 08:19:40','A'),
('100025','cam4','2012-11-15 08:19:40','B'),('100026','cam4','2012-11-15 08:19:40','B'),('100027','cam4','2012-11-15 08:19:40','C');
//可以使用索引
mysql> EXPLAIN
-> select * from data_log where call_date between '2012-11-14 09:00:00' and '2012-11-14 23:59:59';
+----+-------------+----------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+-----------+---------+------+------+-------------+
| 1 | SIMPLE | data_log | range | call_date | call_date | 5 | NULL | 1 | Using where |
+----+-------------+----------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)
//不可以使用索引
mysql> explain select * from data_log where call_date between '2012-11-14 08:00:00' and '2012-11-14 23:59:59';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | data_log | ALL | call_date | NULL | NULL | NULL | 27 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)