ORDER BY doesn't use index

[Frederic Giudicelli]

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.