In the last episode (Mar 13), Bob Silva said:
> Hope this isnt a dupe post...I mailed before confirmation was
> received so I think my post died.
>
> Here is the issue I run the query below and it takes forever ( 1+
> minutes ). It uses 2 keys (one unique) in the where clause but still
> does a full table scan.
>
> mysql> SELECT usernum FROM client WHERE userid='Salty3' OR usernum =
> 4158774796;
> | usernum |
> | 1893996929 |
> 1 row in set (1 min 33.94 sec)
>
> mysql> explain SELECT usernum FROM client WHERE userid='Salty3' OR usernum =
>4158774796;
> | table | type | possible_keys | key | key_len | ref | rows | Extra |
> | client | ALL | PRIMARY,useridIDX | NULL | NULL | NULL | 1586795 | where used |
Mysql can only use one index per table. Since using only one of the
two possible indexes may not return the requested records (what if it
chose the userid index?), it must do a full table scan.
Merging the results of two index scans on one table is tricky (Oracle
will only do it if hinted), but most of the time it's quicker to
just do the full table scan. Your example, of course, is one where it
would help.
--
Dan Nelson
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
