Re: Need help understanding optimizer behavior in 5.6

[Aaron Craven]

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.