That's odd. Based on that explain plan, that query should still take the same order of magnitude of time as your first attempt, as it's having to find 538,427 rows using the status index then filesort them to find the top 90 with the lowest req_ids. It's still using the same index as before too ('status').
It takes a few seconds to run, right?
I still think a multi-column index on (status, req_id) would allow this query to return near-instantly for you.
i.e.
alter table requester add index toastyindex(status, req_id);
alter table requester drop index status; -- you dont need it any more as the toastyindex covers the status column.
analyze table requester;
explain SELECT req_id FROM requester WHERE status='1' ORDER BY req_id ASC LIMIT 0,90;
What does this plan look like? You sure it doesn't help?
Cheers,
Toasty
-----------------------------------------
email: 'toasty'*3 at gmail