StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMYSQL Index on TIMESTAMP field not using index for range query
text
Body
copied!<p>I have the following row in a mysql table</p> <pre><code>+--------------------------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------------------+----------------------+------+-----+---------+----------------+ | created_at | timestamp | YES | MUL | NULL | | </code></pre> <p>The following index exists on the field</p> <pre><code>*************************** 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: </code></pre> <p>I am trying to optimize the following query to use the IDX_My_Table_CREATED_AT index for the range condition</p> <pre><code>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 </code></pre> <p>When I use EXPLAIN on the select query, I get the following:</p> <pre><code>+----+-------------+------------+------+---------------------------------+------+---------+------+--------+-------------+ | 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 | +----+-------------+------------+------+---------------------------------+------+---------+------+--------+-------------+ </code></pre> <p>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. </p> <p>Strangely, if I attempt a single value lookup on the column, the index is used.</p> <pre><code>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 | +----+-------------+------------+------+---------------------------------+---------------------------------+---------+-------+------+-------------+ </code></pre> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload