MySQL Forums
Forum List  »  Performance

Re: Slow performance on join query using indexes
Posted by: Øystein Grøvlen
Date: January 22, 2016 03:02AM


Looking at the query I would think the best join order would be

parentNode -> parentStore -> assoc -> ChildNode -> ChildStore

I am a bit surprised that this order was not chosen, but you can use the STRAIGHT_JOIN hint to force a particular join order. I would think replacing join with straight_join in the last line of the FROM clause should do it:

alf_child_assoc assoc
join alf_node parentNode on ( = assoc.parent_node_id)
join alf_store parentStore on ( = parentNode.store_id)
join alf_node childNode on ( = assoc.child_node_id)
straight_join alf_store childStore on ( = childNode.store_id)

I think forcing childStore after childNode should be sufficient to make the optimizer choose the wanted join order. If not, you can reorder the from clause in the order you want them to be joined in and add STRAIGHT_JOIN right after SELECT.

Alternatively, I would also think that forcing the parent_node_id index to be used for assoc will get things right. That is,

from alf_child_assoc assoc FORCE INDEX(parent_node_id)

Hope this helps. With the row counts given by EXPLAIN, 3000 seconds seems a bit much for this query even with the currently chosen plan. It should not be more than a few million rows to process.

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

Options: ReplyQuote

Written By
Re: Slow performance on join query using indexes
January 22, 2016 03:02AM

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.