Re: Optimizer executes inperformant subquery first

[Øystein Grøvlen]

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