Re: query with bad performance after migration to mysql 5.6

[Øystein Grøvlen]

Hi,

Thanks for the bug report. We will try to reproduce the issue based on data in the optimizer trace. It would be helpful if you could attach the optimizer trace for the query when block_nested_loop is turned off.

As for work-arounds, I suggest you try rewriting your query to a UNION of two separate queries, on for each part of the OR expression of the ON clause. That way, it will always be possible to use an index for the join. (MySQL is not good at handling ON clauses with OR.) That is, something like:

SELECT messaggio.id, messaggio.data, allegati.id
FROM (
select m.id from messaggio m
WHERE m.idarticolo='repnews_271330' AND m.idpadre=0
AND m.pubblica=1 ORDER BY m.data DESC LIMIT 50
) as m_padre
join messaggio on (m_padre.id=messaggio.idpadre)
left join allegati on messaggio.id = allegati.idpost
UNION ALL
SELECT messaggio.id, messaggio.data, allegati.id
FROM (
select m.id from messaggio m
WHERE m.idarticolo='repnews_271330' AND m.idpadre=0
AND m.pubblica=1 ORDER BY m.data DESC LIMIT 50
) as m_padre
join messaggio on (m_padre.id=messaggio.id)
left join allegati on messaggio.id = allegati.idpost
ORDER BY messaggio.data DESC, messaggio.id desc, allegati.id asc;

Thanks,

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