MySQL Forums
Forum List  »  Performance

Re: query with bad performance after migration to mysql 5.6
Posted by: Øystein Grøvlen
Date: December 10, 2015 04:37AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: query with bad performance after migration to mysql 5.6
1159
December 10, 2015 04:37AM


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.