StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL datetime index is not working
text
Body
copied!<p>Table structure:</p> <pre><code>+-------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | total | int(11) | YES | | NULL | | | thedatetime | datetime | YES | MUL | NULL | | +-------------+----------+------+-----+---------+----------------+ </code></pre> <p>Total rows: <strong>137967</strong></p> <pre><code>mysql> explain select * from out where thedatetime <= NOW(); +----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | out | ALL | thedatetime | NULL | NULL | NULL | 137967 | Using where | +----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+ </code></pre> <p>The real query is much more longer with more table joins, the point is, I can't get the table to use the <code>datetime</code> index. This is going to be hard for me if I want to select all data until certain date. However, I noticed that I can get MySQL to use the index if I select a smaller subset of data. </p> <pre><code>mysql> explain select * from out where thedatetime <= '2008-01-01'; +----+-------------+-------------+-------+---------------+-------------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+-------------+---------+------+-------+-------------+ | 1 | SIMPLE | out | range | thedatetime | thedatetime | 9 | NULL | 15826 | Using where | +----+-------------+-------------+-------+---------------+-------------+---------+------+-------+-------------+ mysql> select count(*) from out where thedatetime <= '2008-01-01'; +----------+ | count(*) | +----------+ | 15990 | +----------+ </code></pre> <p>So, what can I do to make sure MySQL will use the index no matter what date that I put?</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