Re: Why is simple query not using index?
On Tue, Mar 3, 2009 at 6:51 PM, Perrin Harkins 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: idselect_typetabletypepossible_keyskeykey_len refrowsExtra 1|SIMPLE|member|range|expiration|expiration|4||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 > 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_QUALIFIERINDEX_NAMEORDINAL_POSITIONCOLUMN_NAME > > ASC_OR_DESCNON_UNIQUETYPECARDINALITYPAGES > > FILTER_CONDITION > > |expiration|1|expiration|A|true|3|102|0| > > > > 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? > > >
Re: Why is simple query not using index?
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 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| > > 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
Why is simple query not using index?
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_QUALIFIERINDEX_NAMEORDINAL_POSITIONCOLUMN_NAME ASC_OR_DESCNON_UNIQUETYPECARDINALITYPAGES FILTER_CONDITION |expiration|1|expiration|A|true|3|102|0| 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?
