Re: ORDER BY doesn't use index

[Toa Sty]

Hi Fred,

The status index is actually used in the first case. The confusion here (which I see all the time and had myself) is that the 'using index' in the extra column does not mean that an index is used for the value lookups or joins etc.

It just means that that part of the query doesn't need to look at the MYD (data) file, it just needs to look at the MYI (index) file to find all the values it needs.

You can tell whether an index is actually used for joining/WHEREing by looking at the 'key' column in the explain output.



As an aside, I suspect in your case that using an index on `status` isn't going to give you great performance - your query finds 1,414,104 rows where status=1 then has to filesort them to give you the latest 90. This is much more work that should be necessary as you can probably tell.

You could try adding an index on (status,req_id) or maybe better, just force the req_id index to be used instead of the status one, so that the query scans in req_id order until 90 rows with status=1 have been found. (rather than finding all 1.4M status=1 rows and having to sort them)

To do this, either use 'from requester USE INDEX(req_id)' or move your status=1 to be a HAVING condition rather than a WHERE condition.

Note that this way, if the query has to scan few rows in req_id order to find 90 with status=1 this will execute very quickly, but if it has to scan many in req_id order to find 90 rows it will be fairly slow. Chances are (depending on the data distribution of status=1 with respect to req_id order) it'll still be much quicker for you than filesorting 1.4M rows.

Try some of the above and post and explain plan and let's see how it goes :)

HTH,
Toasty

PS I wouldn't worry about the subselect method - you shouldn't need to use it.