MySQL Integer vs DateTime index
MysqlIndexingInnodbMysql Problem Overview
Let me start by saying I have looked at many similar questions asked, but all of them relate to Timestamp and DateTime field type without indexing. At least that is my understanding.
As we all know, there are certain advantages when it comes to DateTime. Putting them aside for a minute, and assuming table's engine is InnoDB with 10+ million records, which query would perform faster when criteria is based on:
>1. DateTime with index >2. int with index
In other words, it is better to store date and time as DateTime or UNIX timestamp in int? Keep in mind there is no need for any built-in MySQL functions to be used.
Update
Tested with MySQL 5.1.41 (64bit) and 10 million records, initial testing showed significant speed difference in favour of int. Two tables were used, tbl_dt with DateTime and tbl_int with int column. Few results:
SELECT SQL_NO_CACHE COUNT(*) FROM `tbl_dt`;
+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (2 min 10.27 sec)
SELECT SQL_NO_CACHE COUNT(*) FROM `tbl_int`;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (25.02 sec)
SELECT SQL_NO_CACHE COUNT(*) FROM `tbl_dt` WHERE `created` BETWEEN '2009-01-30' AND '2009-12-30';
+----------+
| COUNT(*) |
+----------+
| 835663 |
+----------+
1 row in set (8.41 sec)
SELECT SQL_NO_CACHE COUNT(*) FROM `tbl_int` WHERE `created` BETWEEN 1233270000 AND 1262127600;
+----------+
| COUNT(*) |
+----------+
| 835663 |
+----------+
1 row in set (1.56 sec)
I'll post another update with both fields in one table as suggested by shantanuo.
Update #2
Final results after numerous server crashes :) Int type is significantly faster, no matter what query was run, the speed difference was more or less the same as results above.
"Strange" thing observed was execution time was more or less the same when two both field types are stored in the same table. It seems MySQL is smart enough to figure out when the values are the same when stored in both DateTime and int. Haven't found any documentation on the subject, therefore is just an observation.
Mysql Solutions
Solution 1 - Mysql
I see that in the test mentioned in the above answer, the author basically proves it that when the UNIX time is calculated in advance, INT wins.
Solution 2 - Mysql
My instinct would be to say that ints are always faster. However, this seems not to be the case
Edited to add: I realize that you're using InnoDB, rather than MyISAM, but I haven't found anything to contradict this in the InnoDB case. Also, the same author did an InnoDB test
Solution 3 - Mysql
it depends on your application, as you can see in an awesome comparison and benchmark of DATETIME , TIMESTAMP and INT type in Mysql server in MySQL Date Format: What Datatype Should You Use? We Compare Datetime, Timestamp and INT. you can see in some situation INT has better perfomance than other and in some cases DATETIME has better performance. and It completely depends on your application