mysql日期用long_mysqltimestamp和long存储时间效率比较

show create table 20130107date;

CREATE TABLE `20130107date` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`c_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`c_date_long` int(20) NOT NULL,

`idx_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

`idx_date_long` int(11) NOT NULL,

PRIMARY KEY (`id`),

KEY `20130107date_idx_date` (`idx_date`),

KEY `20130107date_idx_long` (`idx_date_long`)

) ENGINE=InnoDB

里面有90w数据,都是随机的时间.

先看没有索引的全表扫描

1 :

select COUNT(*) from 20130107date

where c_date BETWEEN DATE('20110101') and DATE('20110102')

这个需要1.54s

2:

select COUNT(*) from 20130107date

where c_date_long BETWEEN UNIX_TIMESTAMP('20110101') and UNIX_TIMESTAMP('20110102'