MySQL Forums
Forum List  »  Optimizer & Parser

ORDER BY doesn't use index
Posted by: Frederic Giudicelli
Date: September 13, 2006 12:32AM

Hi,

Using mysql 4.1.16.
Tables are MyISAM.

The primary key of table "requester" is "req_id", I also have an index on "status".

When I use an "ORDER BY" clause in the following statement the index on "status" is not used:
explain SELECT req_id FROM requester WHERE status='1' ORDER BY req_id ASC LIMIT 0,90;
+----+-------------+-----------+------+---------------+--------+---------+-------+---------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+--------+---------+-------+---------+-----------------------------+
| 1 | SIMPLE | requester | ref | status | status | 4 | const | 1414104 | Using where; Using filesort |
+----+-------------+-----------+------+---------------+--------+---------+-------+---------+-----------------------------+

However the following statement makes use of index on "status":
explain SELECT req_id FROM requester WHERE req_id IN (SELECT req_id FROM requester WHERE status='1') ORDER BY req_id ASC LIMIT 0,90;
+----+--------------------+-----------+-----------------+----------------+---------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------+-----------------+----------------+---------+---------+------+---------+--------------------------+
| 1 | PRIMARY | requester | index | NULL | PRIMARY | 4 | NULL | 1736077 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | requester | unique_subquery | PRIMARY,status | PRIMARY | 4 | func | 1 | Using index; Using where |
+----+--------------------+-----------+-----------------+----------------+---------+---------+------+---------+--------------------------+


The subquery workaround is not really memory efficient but it is the only solution I found, it divides by 10 the execution time of the query. Is there any other solution ?

Regards,
Fred.

Options: ReplyQuote


Subject
Views
Written By
Posted
ORDER BY doesn't use index
12100
September 13, 2006 12:32AM
5333
September 13, 2006 02:00AM
3833
September 13, 2006 02:36AM
3487
September 27, 2006 03:39AM
3322
September 27, 2006 05:02AM
3240
September 30, 2006 05:53PM
3255
October 03, 2006 02:38AM


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.