title: MySQL index on timestamp column not used for large date ranges
body: <p>I have table as</p>
  
  <pre><code>+-------------------+----------------+------+-----+---------------------+-----------------------------+
  | Field             | Type           | Null | Key | Default             | Extra                       |
  +-------------------+----------------+------+-----+---------------------+-----------------------------+
  | id                | bigint(20)     | NO   | PRI | NULL                | auto_increment              |
  | runtime_id        | bigint(20)     | NO   | MUL | NULL                |                             |
  | place_id          | bigint(20)     | NO   | MUL | NULL                |                             |
  | amended_timestamp | varchar(50)    | YES  |     | NULL                |                             |
  | applicable_at     | timestamp      | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
  | schedule_time     | timestamp      | NO   | MUL | 0000-00-00 00:00:00 |                             |
  | quality_indicator | varchar(10)    | NO   |     | NULL                |                             |
  | flow_rate         | decimal(15,10) | NO   |     | NULL                |                             |
  +-------------------+----------------+------+-----+---------------------+-----------------------------+
  </code></pre>
  
  <p>I have index on schedule_time as</p>
  
  <pre><code>create index table_index on table(schedule_time asc);
  </code></pre>
  
  <p>The table currently has 2121552+ records.</p>
  
  <p>The thing I fail to understand is when I do explain</p>
  
  <pre><code>explain select runtime_id from table where schedule_time &gt;= now() - INTERVAL 1 DAY;
  +----+-------------+----------+-------+------------------------------+------------------------------+---------+------+-------+-------------+
  | id | select_type | table    | type  | possible_keys                | key                          | key_len | ref  | rows  | Extra       |
  +----+-------------+----------+-------+------------------------------+------------------------------+---------+------+-------+-------------+
  |  1 | SIMPLE      | table    | range | table_index                  | table_index                  | 4       | NULL | 38088 | Using where |
  +----+-------------+----------+-------+------------------------------+------------------------------+---------+------+-------+-------------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>Above index is used, but the below one not.</p>
  
  <pre><code>mysql&gt; explain select runtime_id from table where schedule_time &gt;= now() - INTERVAL 30 DAY;
  +----+-------------+----------+------+------------------------------+------+---------+------+---------+-------------+
  | id | select_type | table    | type | possible_keys                | key  | key_len | ref  | rows    | Extra       |
  +----+-------------+----------+------+------------------------------+------+---------+------+---------+-------------+
  |  1 | SIMPLE      | table    | ALL  | table_index                  | NULL | NULL    | NULL | 2118107 | Using where |
  +----+-------------+----------+------+------------------------------+------+---------+------+---------+-------------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>I'll really appreciate if someone can point out whats wrong here, as the data is updated every 12 minutes and as the time passes by query for 30 days or may be 60 days will get very slow.</p>
  
  <p>The final query where I plan to use it is as follows</p>
  
  <pre><code>select avg(flow_rate),c.group from table a ,(select runtime_id from table where schedule_time &gt;= now() - INTERVAL 1 DAY group by schedule_time ) b,place c  where a.runtime_id = b.runtime_id and a.place_id = c.id group by c.group;
  </code></pre>
  
  <p><strong>Update =====></strong></p>
  
  <p>As per the comments between fails too.</p>
  
  <pre><code>mysql&gt; explain select runtime_id from table where schedule_time between '2013-07-17 12:48:00' and '2013-08-17 12:48:00';
  +----+-------------+----------+------+------------------------------+------+---------+------+---------+-------------+
  | id | select_type | table    | type | possible_keys                | key  | key_len | ref  | rows    | Extra       |
  +----+-------------+----------+------+------------------------------+------+---------+------+---------+-------------+
  |  1 | SIMPLE      | table    | ALL  | table_index                  | NULL | NULL    | NULL | 2118431 | Using where |
  +----+-------------+----------+------+------------------------------+------+---------+------+---------+-------------+
  1 row in set (0.00 sec)
  
  mysql&gt; explain select runtime_id from table where schedule_time between '2013-08-16 12:48:00' and '2013-08-17 12:48:00';
  +----+-------------+----------+-------+------------------------------+------------------------------+---------+------+-------+-------------+
  | id | select_type | table    | type  | possible_keys                | key                          | key_len | ref  | rows  | Extra       |
  +----+-------------+----------+-------+------------------------------+------------------------------+---------+------+-------+-------------+
  |  1 | SIMPLE      | table    | range | table_index                  | table_index                  | 4       | NULL | 38770 | Using where |
  +----+-------------+----------+-------+------------------------------+------------------------------+---------+------+-------+-------------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p><strong>Update 2 =======></strong></p>
  
  <pre><code>mysql&gt; select count(*) from table where schedule_time between '2013-08-16 12:48:00' and '2013-08-17 12:48:00';
  +----------+
  | count(*) |
  +----------+
  |    19440 |
  +----------+
  1 row in set (0.01 sec)
  
  mysql&gt; select count(*) from table where schedule_time between '2013-07-17 12:48:00' and '2013-08-17 12:48:00';
  +----------+
  | count(*) |
  +----------+
  |   597132 |
  +----------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)</p>