MySQL Forums
Forum List  »  Optimizer & Parser

Re: ORDER BY doesn't use index
Posted by: Toa Sty
Date: September 13, 2006 02:00AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
12182
September 13, 2006 12:32AM
Re: ORDER BY doesn't use index
5367
September 13, 2006 02:00AM
3887
September 13, 2006 02:36AM
3521
September 27, 2006 03:39AM
3399
September 27, 2006 05:02AM
3302
September 30, 2006 05:53PM
3298
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.