From: Perrin Harkins
Date: March 4 2009 2:51am
Subject: Re: Why is simple query not using index?
List-Archive: http://lists.mysql.com/mysql/216567
Message-Id: <66887a3d0903031851o56ccda7fgc25fae2c1286f5fd@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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> wro=
te:
> 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
>> =A0 =A0 -> WHERE expiration < DATE_ADD(CURDATE(), INTERVAL 30 DAY)\G
>> *************************** 1. row ***************************
>> =A0 =A0 =A0 =A0 =A0 =A0id: 1
>> =A0 select_type: SIMPLE
>> =A0 =A0 =A0 =A0 table: MEMBER
>> =A0 =A0 =A0 =A0 =A0type: range
>> possible_keys: expiration
>> =A0 =A0 =A0 =A0 =A0 key: expiration
>> =A0 =A0 =A0 key_len: 4
>> =A0 =A0 =A0 =A0 =A0 ref: NULL
>> =A0 =A0 =A0 =A0 =A0rows: 6
>> =A0 =A0 =A0 =A0 Extra: Using where
>>
>>
> Unfortunately, that's not the output I'm getting. =A0It's actually this:
>
> mysql> EXPLAIN SELECT * FROM MEMBER
>> =A0 =A0 -> WHERE expiration < DATE_ADD(CURDATE(), INTERVAL 30 DAY)\G
>> *************************** 1. row ***************************
>> =A0 =A0 =A0 =A0 =A0 =A0id: 1
>> =A0 select_type: SIMPLE
>> =A0 =A0 =A0 =A0 table: MEMBER
>> =A0 =A0 =A0 =A0 =A0type: all
>> possible_keys: expiration
>> =A0 =A0 =A0 =A0 =A0 key: NULL
>> =A0 =A0 =A0 key_len: NULL
>> =A0 =A0 =A0 =A0 =A0 ref: NULL
>> =A0 =A0 =A0 =A0 =A0rows: 102
>> =A0 =A0 =A0 =A0 Extra: Using where
>>
>> =A0Copying the index info from SQuirreL, it is:
>
> INDEX_QUALIFIER =A0 =A0INDEX_NAME =A0 =A0ORDINAL_POSITION =A0 =A0COLUMN_N=
AME
> ASC_OR_DESC =A0 =A0NON_UNIQUE =A0 =A0TYPE =A0 =A0CARDINALITY =A0 =A0PAGES
> FILTER_CONDITION
> =A0|expiration|1|expiration|A|true|3|102|0|<null>
>
> It's a bit hard to read, but I replaced tab characters with "|" between e=
ach
> column.
>
> Why might this query not be behaving as I expect?
>