List:General Discussion« Previous MessageNext Message »
From:Perrin Harkins Date:March 4 2009 2:51am
Subject:Re: Why is simple query not using index?
View as plain text  
My guess would be that your table is too small to bother using an
index on.  There's some information in the MySQL docs about when it
chooses to use an index.  For small tables, using one makes the query
slower.

- Perrin

On Tue, Mar 3, 2009 at 7:58 PM, David Karr <davidmichaelkarr@stripped> wrote:
> 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