CODEWITHSUNDEEP
mysqlindexingexplain
toom
toom

Reputation: 13316

MySQL query ignores index for a where clause

I've got a table with a column called "date".

The table looks somthing like this

CREATE TABLE IF NOT EXISTS `offers_log_archive` (
                    ...
                    `date` date DEFAULT NULL,
                    ...
                    KEY `date` (`date`)
                ) ENGINE=InnoDB

I perform the following query on this table:

SELECT 
    *
FROM 
    offers_log_archive as ola
WHERE 
    ola.date >= "2012-12-01" and
    ola.date <= "2012-12-31"

Then I did the following:

explain (SELECT 
    *
FROM 
    offers_log_archive as ola
WHERE 
    ola.date >= "2012-12-01" and
    ola.date <= "2012-12-31" );

The result of this explain is

id  select_type  table type possible_keys key  key_len ref  rows    Extra
1   SIMPLE       ola   ALL  date          NULL NULL    NULL 6206460 Using where

Why do I get type ALL? From what I know that basically means that the query will inspect every row in the table and ignores the index on date. Although I would expect that mysql would use this.

What happens here and why is the date index ignored?

Upvotes: 1

Views: 1164

Answers (1)

Bohemian
Bohemian

Reputation: 424963

Almost all values in your column are within the range of the query, so not only would the index be next to useless (it would add little value), but it would actually be much more expensive to use the index than do a simple table scan.

Edit

Try first running ANALYZE on the table:

ANALYZE TABLE MYTABLE

If that doesn't help, try changing the syntax to use BETWEEN:

WHERE ola.date BETWEEN '2012-12-01' AND '2012-12-31'

Upvotes: 1

Related Questions