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.