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.

how many records do you have? sometimes MySQL does not use indexes as it is more efficient to scan the table for small tables — Sam
– Sam, Commented Dec 24, 2013 at 17:52
The table has over 273,000 records and data_length of over 180MB, so I don't think that's the issue — Sean
– Sean, Commented Dec 24, 2013 at 17:54
have you tried using the same query with BETWEEN and see if it uses the index? — Sam
– Sam, Commented Dec 24, 2013 at 17:56
Yes, I tried using BETWEEN and it didn't change the execution at all — Sean
– Sean, Commented Dec 24, 2013 at 17:56
I think the problem is that you are passing date literals as strings. try str_to_date() function for the dates I'll put it as an answer — Sam
– Sam, Commented Dec 24, 2013 at 17:59

Raymond Nijland · Accepted Answer · 2013-12-24 18:35:52Z

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..

Collectives™ on Stack Overflow

MYSQL Index on TIMESTAMP field not using index for range query

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related