MySQL Forums
Forum List  »  Optimizer & Parser

Re: indices not used
Posted by: Rick James
Date: May 27, 2010 08:35PM

I guess this is MyISAM, not InnoDB?

It is more efficient to do a table scan (read all the MYD file) and sort the result, than to bounce between the MYI (with the index) and the MYD. Hence the preference for ignoring the index on
SELECT * FROM tbl ORDER BY some_key_field;

You products query would have done the same if it saw (by probing the index) that you were probably going to get more than xx% of the table. (xx is somewhere between 10 and 30, depending on the phase of the moon.)

ALTER TABLE product ORDER BY factor;
will "cluster" rows so that nearby values of y are probably in the same disk block. This can be a big benefit if the table is huge and you fetch values near y. It is a benefit because of decreasing disk I/O; the explain plan won't change.

You must always use ORDER BY if you want to be sure that the data is returned in that order. Granted, there are cases where the data seems to always be correctly ordered, but that is today; it could change tomorrow.

ORDER BY is optimized out if the optimizer can guarantee the order without actually doing a sort. ALTER will not provide that guarantee.

Options: ReplyQuote

Written By
May 25, 2010 04:13AM
May 25, 2010 12:07PM
Re: indices not used
May 27, 2010 08:35PM
June 19, 2010 03:35AM
June 21, 2010 06:25PM
June 22, 2010 01:15AM

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.