Re: Need help understanding optimizer behavior in 5.6

[Aaron Craven]

> However, I expect they might
> ask you if there is some way to reproduce this.
> Maybe you can either upload a compressed dump of
> your database or share the scripts you used to
> generate it.

I can absolutely do either of these.

> Wrt what I wrote about 50% lower row estimates:
> ...
> 24943/7=3563. Hence, this plan appears to the
> optimizer to be less costly than it actually is.
> This "faking" of statistics have been removed from
> MySQL 5.7.

I had wondered why it estimated 14 instead of 7. I had assumed this was simply because it was an estimate as opposed to an exact value.

> Looking at the optimizer trace, we can see that it
> is actually this issue that cause the optimizer to
> choose the wrong query plan.

I need to spend a little more time working through your explanation to make sure I understand it, but this definitely seems to make sense.

> In other words, I expect your issue to be fixed in
> MySQL 5.7.

That is excellent news! Is there a planned schedule for GA releases? Also (and this probably isn't a question for this forum) do you know how long after a GA release it will be before MySQL Enterprise offers 5.7 as a supported version?

> However, I am a bit surprised that
> you also see this issue in the production database
> where you say your filter is much more selective.

As it turns out, it wasn't. That problem (which I was trying to model, but failed) turned out to be a problem with bad index cardinality on one of the indexes. To solve it, we simply had to increase stats_sample_pages on the table in question and the access path problem resolved.

> Hope this helps

This helped a great deal. Thank you very much!