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?
Both queries by themself run fine as shown below. In this instance, the userid Salty3
does
exist in the table whereas the usernum shown does not. Usernum is an int unsigned
field and userid
is a varchar(254)
mysql> show keys from client;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
|Cardinality | Sub_part | Packed | Comment |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| client | 0 | PRIMARY | 1 | usernum | A |
|1586819 | NULL | NULL | |
| client | 1 | useridIDX | 1 | userid | A |
|NULL | 10 | NULL | |
| client | 1 | passwdIDX | 1 | password | A |
|NULL | 5 | NULL | |
| client | 1 | emailIDX | 1 | email | A |
|NULL | 7 | NULL | |
| client | 1 | signupIDX | 1 | signup | A |
|NULL | NULL | NULL | |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+
5 rows in set (0.04 sec)
By itself searching on userid, it uses the right key
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.04 sec)
Dont think this is a big deal as I think I've seen it before....usernum is the Primary
Key
mysql> explain SELECT usernum FROM client WHERE usernum=4158774796;
+-----------------------------------------------------+
| Comment |
+-----------------------------------------------------+
| Impossible WHERE noticed after reading const tables |
+-----------------------------------------------------+
1 row in set (0.04 sec)
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.
Bob @bravenet.com
