MySQL Forums
Forum List  »  Newbie

Indexing headache
Posted by: Kenneth -
Date: April 20, 2005 02:38AM

I have a fairly simple query that's bugging me

I want to fetch all rows from the table messages where the to or copy fields are matching a specific id. I didn't think this would be a problem, but I noticed after a while that my web application preformed extremely poorly on this operation. Not really wierd when it's using a full-table scan.

Here's what I want to fetch, but it's not using the keys listed in possible_keys:
mysql> explain select * from `messages` where `to`='2' or `copy`='2';
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | messages | ALL | to,copy | NULL | NULL | NULL | 18977 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

If I omit the or operator and leave out the "copy" or "to" field, it'll use indexing.
mysql> explain select * from `messages` where `to`='2';
+----+-------------+----------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | messages | ref | to | to | 2 | const | 9445 | |
+----+-------------+----------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)

I tried using FORCE INDEX as well, but it doesn't want to.
mysql> explain select * from `messages` force index (`to`,`copy`) where `to`='1' or `copy`='1';
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | messages | ALL | to,copy | NULL | NULL | NULL | 18977 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

Any comments ?

Any ideas why it doesn't use indexing here ?

Options: ReplyQuote


Subject
Written By
Posted
Indexing headache
April 20, 2005 02:38AM
April 20, 2005 10:04AM
April 20, 2005 10:12AM


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.