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 <davidmichaelk...@gmail.com> 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? > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
