If it thinks it will retrieve more than a third of the records, the
index will not be used.
[EMAIL PROTECTED] 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 <[EMAIL PROTECTED]>
>To unsubscribe, e-mail
><[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
