title: MySQL Integer vs DateTime index
body: <p>Let me start by saying I have looked at many similar questions asked, but all of them relate to <code>Timestamp</code> and <code>DateTime</code> field type without indexing. At least that is my understanding. </p>
  
  <p>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 <code>InnoDB</code> with <code>10+ million records</code>, which query would perform faster when criteria is based on:</p>
  
  <blockquote>
    <ol>
    <li>DateTime with index</li>
    <li>int with index</li>
    </ol>
  </blockquote>
  
  <p>In other words, it is better to store date and time as <code>DateTime</code> or UNIX timestamp in <code>int</code>? Keep in mind there is no need for any built-in MySQL functions to be used.</p>
  
  <p><strong>Update</strong></p>
  
  <p>Tested with MySQL 5.1.41 (64bit) and 10 million records, initial testing showed significant speed difference in favour of <code>int</code>. Two tables were used, <code>tbl_dt</code> with <code>DateTime</code> and <code>tbl_int</code> with <code>int</code> column. Few results:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>I'll post another update with both fields in one table as suggested by <a href="https://stackoverflow.com/users/139150/">shantanuo</a>.</p>
  
  <p><strong>Update #2</strong></p>
  
  <p>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.</p>
  
  <p>"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.</p>