On Tue, Mar 13, 2001 at 08:03:09PM -0800, Bob Silva wrote:
> Heres the query Im trying to run: takes about 2 minutes...both userid and usernum
>columns have indexes...usernum is unique. Show keys follows.
> 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 | 1586803 | where used |
> +--------+------+-------------------+------+---------+------+---------+------------+
> 1 row in set (0.04 sec)
>
>
> Why is this doing a full table scan when I have provided 2 keys to search on?
MySQL doesn't optimize WHERE clauses with OR's in them (at least not as good as
it optimizes queries without them). See also:
http://www.mysql.com/doc/S/e/Searching_on_two_keys.html
...
> Heres the time it took to run the query...we have about 1.6 million records in this
>table...it would make sense to me it would be able to use either key
>successively...but then again I didnt develop mysql so thats why Im here.
>
Yes, it would need to use the indexes successively AND take care to use records
only once if they exist in both indexes.
Fred.
--
Fred van Engen XO Communications B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
---------------------------------------------------------------------
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
