List:General Discussion« Previous MessageNext Message »
From:David Karr Date:March 4 2009 9:46pm
Subject:Re: Why is simple query not using index?
View as plain text  
On Tue, Mar 3, 2009 at 6:51 PM, Perrin Harkins <perrin@stripped> wrote:

> 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.


I think this is likely the key point. When I changed the query to:

    explain select count(*) from member force index (expiration) where
expiration < date_add(CURDATE(), interval 30 day)

it gave me this:

id    select_type    table    type    possible_keys    key    key_len
ref    rows    Extra
1|SIMPLE|member|range|expiration|expiration|4|<null>|26|Using where; Using
index

This used the index, and the number of rows addressed is the correct number
of rows.

I found information in the MySQL docs (7.4.5. How MySQL Uses Indexes) that
says it might not use an index if it determines that a table scan would be
less expensive, but nothing that says specifically when this would happen
(which doesn't surprise me).



>
> - 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