MySQL Forums
Forum List  »  Optimizer & Parser

Re: Need help understanding optimizer behavior in 5.6
Posted by: Aaron Craven
Date: July 01, 2015 06:48AM

> 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!

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.