Two possible indexes in simple select but none is used
I have a table with two indexes: one primary on column ID and one non-unique on column UNIQUE_ID. The second index is with specified prefix length 9.
When running select from the table with OR in where clause, MySQL doesn't use any of the indexes. Why?
mysql> explain select * from FOO where id = 0 or unique_id = 'blah';
+----+-------------+--------+------+------------------------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | FOO | ALL | PRIMARY,FOO_unique_id_idx | NULL | NULL | NULL | 149920 | Using where |
+----+-------------+--------+------+------------------------------+------+---------+------+--------+-------------+
1 row in set, 1 warning (0.00 sec)
MySQL version is 4.1.16
Subject
Views
Written By
Posted
Two possible indexes in simple select but none is used
3363
November 28, 2006 10:40AM
1982
November 28, 2006 06:27PM
1938
December 13, 2006 04:41AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.