Re: Need help understanding optimizer behavior in 5.6
> 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!
Subject
Views
Written By
Posted
2330
June 22, 2015 08:30AM
1135
June 22, 2015 08:31AM
1144
June 23, 2015 07:00PM
1136
June 24, 2015 06:22AM
1106
June 25, 2015 11:37PM
1149
June 26, 2015 06:56AM
1116
June 30, 2015 05:46AM
1634
June 30, 2015 08:06AM
1097
June 30, 2015 08:16AM
1323
July 01, 2015 03:49AM
Re: Need help understanding optimizer behavior in 5.6
1279
July 01, 2015 06:48AM
1120
July 01, 2015 03:05PM
1177
July 02, 2015 07:48AM
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.