MySQL Forums
Forum List  »  Optimizer & Parser

Two possible indexes in simple select but none is used
Posted by: Ozkan Mehmedali
Date: November 28, 2006 10:40AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Two possible indexes in simple select but none is used
3363
November 28, 2006 10:40AM


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.