Further to your comment, I have had considerable benefits by creating an
index of both keys as well. So I define 3 indexes userid, usernum,
userid&usernum.
hope this helps.
len
-----Original Message-----
From: Dan Nelson [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 14 March 2001 16:06
To: Bob Silva
Cc: [EMAIL PROTECTED]
Subject: Re: Any ideas why this would happen....
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
