Re: Slow performance on join query using indexes

[Øystein Grøvlen]

Hi,

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:

from
alf_child_assoc assoc
join alf_node parentNode on (parentNode.id = assoc.parent_node_id)
join alf_store parentStore on (parentStore.id = parentNode.store_id)
join alf_node childNode on (childNode.id = assoc.child_node_id)
straight_join alf_store childStore on (childStore.id = 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