表:DROP TABLE IF EXISTS `active`;
CREATE TABLE IF NOT EXISTS `active` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userid` int(10) unsigned NOT NULL,
`lastactive` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `lastactive` (`lastactive`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;插入数据:insert into active values
(null,10000, unix_timestamp("2012-08-20 15:10:02")),
(null,10001, unix_timestamp("2012-08-20 15:10:02")),
(null,10002, unix_timestamp("2012-08-20 15:10:03")),
(null,10003, unix_timestamp("2012-08-20 15:10:03")),
(null,10004, unix_timestamp("2012-08-20 15:10:03")),
(null,10005, unix_timestamp("2012-08-20 15:10:04")),
(null,10006, unix_timestamp("2012-08-20 15:10:04")),
(null,10007, unix_timestamp("2012-08-20 15:10:05")),
(null,10008, unix_timestamp("2012-08-20 15:10:06"))
explain
select * from active where lastactive > unix_timestamp()-3;上面这句索引起作用。
但是我在测试中,因为插入的日期与我测试的当前日期相差不少时间。所以我改写为以下内容:explain
select * from active where lastactive > unix_timestamp("2012-08-20 15:10:06") - 3;但是数据显示,TYPE为ALL,key为NULL。也就是说索引不起作用。我在改写以下语句测试:explain
select * from active where lastactive > unix_timestamp("2012-08-20 15:10:06");上面这个语句,索引又起作用了。请了解这块内容的朋友帮忙解惑~万分感谢。
CREATE TABLE IF NOT EXISTS `active` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userid` int(10) unsigned NOT NULL,
`lastactive` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `lastactive` (`lastactive`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;插入数据:insert into active values
(null,10000, unix_timestamp("2012-08-20 15:10:02")),
(null,10001, unix_timestamp("2012-08-20 15:10:02")),
(null,10002, unix_timestamp("2012-08-20 15:10:03")),
(null,10003, unix_timestamp("2012-08-20 15:10:03")),
(null,10004, unix_timestamp("2012-08-20 15:10:03")),
(null,10005, unix_timestamp("2012-08-20 15:10:04")),
(null,10006, unix_timestamp("2012-08-20 15:10:04")),
(null,10007, unix_timestamp("2012-08-20 15:10:05")),
(null,10008, unix_timestamp("2012-08-20 15:10:06"))
explain
select * from active where lastactive > unix_timestamp()-3;上面这句索引起作用。
但是我在测试中,因为插入的日期与我测试的当前日期相差不少时间。所以我改写为以下内容:explain
select * from active where lastactive > unix_timestamp("2012-08-20 15:10:06") - 3;但是数据显示,TYPE为ALL,key为NULL。也就是说索引不起作用。我在改写以下语句测试:explain
select * from active where lastactive > unix_timestamp("2012-08-20 15:10:06");上面这个语句,索引又起作用了。请了解这块内容的朋友帮忙解惑~万分感谢。
unix_timestamp("2012-08-20 15:10:06") - 2;
unix_timestamp("2012-08-20 15:10:06") - 1;
unix_timestamp("2012-08-20 15:10:06") - 0;
unix_timestamp("2012-08-20 15:10:06") + 1 ;
select * from 表名 where intTime > unix_timestamp() - 24*3600*5-44559;
intTime为索引。24*3600*5-44559这个我是调试用的。结果是超过1854条记录就不走索引了。而进行全表扫描。这个做怎么解释?