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 :)

mysql indexing innodb
Edit
Report