On Tue, Mar 3, 2009 at 6:51 PM, Perrin Harkins <[email protected]> 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 <[email protected]>
> 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?
> >
>
