MYSQL Index on TIMESTAMP field not using index for range query

Question

I have the following row in a mysql table

+--------------------------------+----------------------+------+-----+---------+----------------+
| Field                          | Type                 | Null | Key | Default | Extra          |
+--------------------------------+----------------------+------+-----+---------+----------------+
| created_at                     | timestamp            | YES  | MUL | NULL    |                |

The following index exists on the field

*************************** 6. row ***************************
        Table: My_Table
   Non_unique: 1
     Key_name: IDX_My_Table_CREATED_AT
 Seq_in_index: 1
  Column_name: created_at
    Collation: A
  Cardinality: 273809
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment:

I am trying to optimize the following query to use the IDX_My_Table_CREATED_AT index for the range condition

SELECT * FROM My_Table as main_table  WHERE ((main_table.created_at >= '2013-07-01 05:00:00') AND (main_table.created_at <= '2013-11-09 05:59:59'))\G

When I use EXPLAIN on the select query, I get the following:

+----+-------------+------------+------+---------------------------------+------+---------+------+--------+-------------+
| id | select_type | table      | type | possible_keys                   | key  | key_len | ref  | rows   | Extra       |
+----+-------------+------------+------+---------------------------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | main_table | ALL  | IDX_My_Table_CREATED_AT         | NULL | NULL    | NULL | 273809 | Using where |
+----+-------------+------------+------+---------------------------------+------+---------+------+--------+-------------+

The issue is that the IDX_My_Table_CREATED_AT index is not being used for this range condition, even though it is a BTREE index and therefore should be applicable to the query.

Strangely, if I attempt a single value lookup on the column, the index is used.

EXPLAIN SELECT * FROM My_Table as main_table  WHERE (main_table.created_at = '2013-07-01 05:00:00');
+----+-------------+------------+------+---------------------------------+---------------------------------+---------+-------+------+-------------+
| id | select_type | table      | type | possible_keys                 | key                           | key_len | ref   | rows | Extra       |
+----+-------------+------------+------+---------------------------------+---------------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | main_table | ref  | IDX_My_Table_CREATED_AT index | IDX_My_Table_CREATED_AT index | 5       | const |    1 | Using where |
+----+-------------+------------+------+---------------------------------+---------------------------------+---------+-------+------+-------------+

Why isn't the index being used for the range condition? I have tried changing the query to use BETWEEN but that didn't change anything.

Answer 1

The answer is simple..

The MySQL optimizer is cost based and the optimizer calculated that an full table scan was the best way (cheapest) to fetch the records.

Because the range needed looks like to be the complete table looking into the rows (EXPLAIN) and Cardinality. these numbers are equal.

If the MySQL optimizer did use an index the relative cost would be much higher because of the random reads (slow) what are necessary to look up the records

motto off the story an FULL TABLE SCAN in this case is not the end off the world... just accept it..