Re: query with bad performance after migration to mysql 5.6

[Øystein Grøvlen]

Hi,

Thanks for the trace.

One thing that has changed from 5.5 to 5.6 is that it is possible to use join buffering with outer join. I suspect that the optimizer will prefer join buffering over "Range checked for each record" that is used in 5.5. Hence, I suggest you try to turn off join buffering before running the query by setting

set optimizer_switch = 'block_nested_loop=off'

I would be interested in seeing the trace with this setting to.

Regardless, I hope you will file a bug report for this issue, and, if possible, provide data so that we can reproduce it. Based on the trace I see that the optimizer gets a much lower cost for the join order
messagio -> allegati -> m_padre
than for
messagio -> m_padre -> allegati

I would think that those join orders should be pretty equivalent so there is something strange going on.

Thanks,

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