indexing - MySQL Integer vs DateTime index

Question

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.

Answer 1

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.