List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:September 20 2002 8:28pm
Subject:Re: INDEX not getting used
View as plain text  
If it thinks it will retrieve more than a third of the records, the 
index will not be used.

mike.miller@stripped wrote:

>I get the following results when doing a SELECT off a single table I have.
>What's bizarre is that the index is no longer used when i expand my list of
>states to be included.
>
>mysql> EXPLAIN SELECT * FROM shipping USE INDEX (state) WHERE state IN
>('TN','NY');
>+------------------+-------+---------------+------------+---------+------+--
>----+------------+
>| table            | type  | possible_keys | key        | key_len | ref  |
>rows | Extra      |
>+------------------+-------+---------------+------------+---------+------+--
>----+------------+
>| ship_to_customer | range | state_code    | state_code |       3 | NULL |
>433 | where used |
>+------------------+-------+---------------+------------+---------+------+--
>----+------------+
>
>mysql> EXPLAIN SELECT * FROM shipping USE INDEX (state) WHERE state IN
>('TN','NY','MN','LA');
>+------------------+------+---------------+------+---------+------+------+--
>----------+
>| table            | type | possible_keys | key  | key_len | ref  | rows |
>Extra      |
>+------------------+------+---------------+------+---------+------+------+--
>----------+
>| ship_to_customer | ALL  | state_code    | NULL |    NULL | NULL | 2298 |
>where used |
>+------------------+------+---------------+------+---------+------+------+--
>----------+
>
>Anyone have any suggestions as to where I could look to fix this up?
>
>-- Mike Miller
>
>--
>Happy 20'th Birthday, Mr. Smiley  :-)
>http://research.microsoft.com/~mbj/Smiley/Smiley.html
>
>
>---------------------------------------------------------------------
>Before posting, please check:
>   http://www.mysql.com/manual.php   (the manual)
>   http://lists.mysql.com/           (the list archive)
>
>To request this thread, e-mail <mysql-thread120162@stripped>
>To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


Thread
INDEX not getting usedmike.miller20 Sep
  • Re: INDEX not getting usedGerald Clark21 Sep