List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:March 14 2001 5:05am
Subject:Re: Any ideas why this would happen....
View as plain text  
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
	dnelson@stripped
Thread
Any ideas why this would happen....Bob Silva14 Mar
  • Re: Any ideas why this would happen....Dan Nelson14 Mar
RE: Any ideas why this would happen....Leonard Coonan14 Mar
  • Re: Any ideas why this would happen....Jeremy D. Zawodny14 Mar