title: MySQL datetime index is not working
body: <p>Table structure:</p>
  
  <pre><code>+-------------+----------+------+-----+---------+----------------+
  | Field       | Type     | Null | Key | Default | Extra          |
  +-------------+----------+------+-----+---------+----------------+
  | id          | int(11)  | NO   | PRI | NULL    | auto_increment |
  | total       | int(11)  | YES  |     | NULL    |                |
  | thedatetime | datetime | YES  | MUL | NULL    |                |
  +-------------+----------+------+-----+---------+----------------+
  </code></pre>
  
  <p>Total rows: <strong>137967</strong></p>
  
  <pre><code>mysql&gt; explain select * from out where thedatetime &lt;= NOW();
  +----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
  | id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
  +----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
  |  1 | SIMPLE      | out         | ALL  | thedatetime   | NULL | NULL    | NULL | 137967 | Using where |
  +----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
  </code></pre>
  
  <p>The real query is much more longer with more table joins, the point is, I can't get the table to use the <code>datetime</code> index. This is going to be hard for me if I want to select all data until certain date. However, I noticed that I can get MySQL to use the index if I select a smaller subset of data. </p>
  
  <pre><code>mysql&gt; explain select * from out where thedatetime &lt;= '2008-01-01';
  +----+-------------+-------------+-------+---------------+-------------+---------+------+-------+-------------+
  | id | select_type | table       | type  | possible_keys | key         | key_len | ref  | rows  | Extra       |
  +----+-------------+-------------+-------+---------------+-------------+---------+------+-------+-------------+
  |  1 | SIMPLE      | out         | range | thedatetime   | thedatetime | 9       | NULL | 15826 | Using where |
  +----+-------------+-------------+-------+---------------+-------------+---------+------+-------+-------------+
  
  mysql&gt; select count(*) from out where thedatetime &lt;= '2008-01-01';
  +----------+
  | count(*) |
  +----------+
  |    15990 |
  +----------+
  </code></pre>
  
  <p>So, what can I do to make sure MySQL will use the index no matter what date that I put?</p>