MySQL Forums
Forum List  »  Optimizer & Parser

Re: Need help understanding optimizer behavior in 5.6
Posted by: Aaron Craven
Date: June 24, 2015 06:22AM

Rick James Wrote:
-------------------------------------------------------
> > where e.uneven_val between 300 and 32000
>
> What fraction of `e` does that represent in your
> small test? If it is most of the table, then the
> optimizer may have decided not to bother with
> INDEX(uneven_val).

Exact numbers are 2443 records out of a total of 25000 (so a little less than 10%). As noted, ~90% of the records in the table have the value of 212 for this field. I purposely created the field with an uneven distribution, as that seems to be related to the problem in some way. Granted, this test is exaggerated slightly from the production database it's modeling.

> You could create a separate database on the
> production system for running this test in. That
> _might_ show you that 5.0 did the same thing. (Or
> it might not.)

I don't currently have permissions to make any modifications to the production server, unfortunately. However, the "real" database this is modeling is reflected on both servers, and 5.0 definitely gets a different access plan in that situation.

Options: ReplyQuote




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.