MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimizer executes inperformant subquery first
Posted by: Øystein Grøvlen
Date: December 23, 2013 08:07AM

Hi Martin,

I have been able to reproduce both your query plan issue and optimizer trace issue by using the database dump that you uploaded to Bug#71171.

The reason for the trace issue seems to be some character set problem. MySQL tries to interpret the byte string in the query as UTF8 when generating the trace, and I think it fails becuase the byte string is not valid UTF8.

I got around that issue by chaging the server to not output the constants used for range scans in the trace. Then I got a valid optimizer trace, and I found the following in the trace when arriving at a prefix of the "good" plan:

"pruned_by_eq_ref_heuristic": true

Not yet sure exactly what happens here, but for some reason the good planned is pruned by the join optimizer. Fortunately, it is possible to turn off this pruning by setting a variable:

set optimizer_prune_level = 0;

Now I get the good plan. You see if this helps in your case.
I will fill in with more details after the holidays.

Thanks for giving us such an interesting test case.

Happy holidays,

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway

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.