List:General Discussion« Previous MessageNext Message »
From:David Karr Date:March 4 2009 12:58am
Subject:Why is simple query not using index?
View as plain text  
I'm using MySQL 5.0.67-0ubuntu6.

I'm stepping through "MySQL - 4th Edition". There's a simple table called
"member" that we've just added an index to, for the "expiration" column,
which is a date column.

The current example in the book is:

mysql> EXPLAIN SELECT * FROM MEMBER
>     -> WHERE expiration < DATE_ADD(CURDATE(), INTERVAL 30 DAY)\G
> *************************** 1. row ***************************
>            id: 1
>   select_type: SIMPLE
>         table: MEMBER
>          type: range
> possible_keys: expiration
>           key: expiration
>       key_len: 4
>           ref: NULL
>          rows: 6
>         Extra: Using where
>
>
Unfortunately, that's not the output I'm getting.  It's actually this:

mysql> EXPLAIN SELECT * FROM MEMBER
>     -> WHERE expiration < DATE_ADD(CURDATE(), INTERVAL 30 DAY)\G
> *************************** 1. row ***************************
>            id: 1
>   select_type: SIMPLE
>         table: MEMBER
>          type: all
> possible_keys: expiration
>           key: NULL
>       key_len: NULL
>           ref: NULL
>          rows: 102
>         Extra: Using where
>
>  Copying the index info from SQuirreL, it is:

INDEX_QUALIFIER    INDEX_NAME    ORDINAL_POSITION    COLUMN_NAME
ASC_OR_DESC    NON_UNIQUE    TYPE    CARDINALITY    PAGES
FILTER_CONDITION
 |expiration|1|expiration|A|true|3|102|0|<null>

It's a bit hard to read, but I replaced tab characters with "|" between each
column.

Why might this query not be behaving as I expect?

Thread
Why is simple query not using index?David Karr4 Mar
  • Re: Why is simple query not using index?Perrin Harkins4 Mar
    • Re: Why is simple query not using index?David Karr4 Mar