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)
Since this uses keys it doesnt make sense to me at least that it would take
so long.
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 |
+--------+------+-------------------+------+---------+------+---------+-----
-------+
1 row in set (0.05 sec)
mysql> explain SELECT usernum FROM client WHERE userid='Salty3';
+--------+------+---------------+-----------+---------+-------+------+------
------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+--------+------+---------------+-----------+---------+-------+------+------
------+
| client | ref | useridIDX | useridIDX | 10 | const | 1 | where
used |
+--------+------+---------------+-----------+---------+-------+------+------
------+
1 row in set (0.05 sec)
mysql> explain SELECT usernum FROM client WHERE usernum = '4158774796';
+-----------------------------------------------------+
| Comment |
+-----------------------------------------------------+
| Impossible WHERE noticed after reading const tables |
Both of these when run individually with only one key run fine and return
the values expected.
mysql> select usernum from client where userid='Salty3';
+------------+
| usernum |
+------------+
| 1893996929 |
+------------+
1 row in set (0.03 sec)
mysql> select usernum from client where usernum=4158774796;
Empty set (0.04 sec)
So I'm kinda at a loss why doing a select using both keys in the where would
do a full table scan and not use any keys.
Thanks for any help. I can use our support contract if this requires more
than a simple answer.
Bob @bravenet.com
---------------------------------------------------------------------
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
