MySQL timestamp and long storage time efficiency comparison

 Show create table 20130107date;CREATE TABLE ' 20130107date '(' id ' int( One) not NULLAuto_increment,' C_date ' timestamp  not NULL DEFAULT Current_timestamp,' C_date_long ' int( -) not NULL,' Idx_date ' timestamp  not NULL DEFAULT ' 0000-00-00 00:00:00 ',' Idx_date_long ' int( One) not NULL,PRIMARY KEY(' id '),KEY ' 20130107date_idx_date '(' Idx_date '),KEY ' 20130107date_idx_long '(' Idx_date_long ')) Engine=innodb

There are 90w of data, which are random times.
See full table scan without index first

1:

select COUNT(*) from 20130107datewhere c_date BETWEEN DATE(‘20110101‘) and DATE(‘20110102‘)

This needs 1.54s.

2:

select COUNT(*) from 20130107datewhere c_date_long BETWEEN UNIX_TIMESTAMP(‘20110101‘) and UNIX_TIMESTAMP(‘20110102‘)

This is 2.3s.

But you can do this.
5 |

select UNIX_TIMESTAMP(‘20110101‘),UNIX_TIMESTAMP(‘20110102‘);

Get Results 1293811200 and 1293897600

And then

select COUNT(*) from 20130107datewhere c_date_long BETWEEN 1293811200 and 1293897600；

Discovery turned into a 0.61s
The gap between 1 and 2 can also be said to be compared to the difference between int and compare TIMESTAMP, then the gap between 2 and 3? Is there more time for every record to evaluate Unix_timestamp (' 20110102 ')?

Then use the index

select COUNT(*) from 20130107datewhere idx_date_long BETWEEN UNIX_TIMESTAMP(‘20110101‘) and UNIX_TIMESTAMP(‘20110102‘);select COUNT(*) from 20130107datewhere idx_date BETWEEN ‘20110101‘ and ‘20110102‘

No suspense, two basic is instantaneous.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL timestamp and long storage time efficiency comparison