Slow performance on join query using indexes

[Amit Kapoor]

Hello Folks,

We use Mysql 5.5.28 with InnoDB version 1.1.8
One of our queries fired by our alfresco application seems to take too long to return results ~3000secs.

Query looks like-

select
            assoc.id                    as id,
            parentNode.id               as parentNodeId,
            parentNode.version          as parentNodeVersion,
            parentStore.protocol        as parentNodeProtocol,
            parentStore.identifier      as parentNodeIdentifier,
            parentNode.uuid             as parentNodeUuid,
            childNode.id                as childNodeId,
            childNode.version           as childNodeVersion,
            childStore.protocol         as childNodeProtocol,
            childStore.identifier       as childNodeIdentifier,
            childNode.uuid              as childNodeUuid,
            assoc.type_qname_id         as type_qname_id,
            assoc.child_node_name_crc   as child_node_name_crc,
            assoc.child_node_name       as child_node_name,
            assoc.qname_ns_id           as qname_ns_id,
            assoc.qname_localname       as qname_localname,
            assoc.is_primary            as is_primary,
            assoc.assoc_index           as assoc_index
        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)
            join alf_store childStore on (childStore.id = childNode.store_id)
 
        where
            parentNode.id = ?

The QUERY PLAN the server shows for this is-

select_type	table		type	possible_keys						key		key_len	        ref				rows	Extra
SIMPLE		parentNode	const	PRIMARY,store_id,fk_alf_node_store	                PRIMARY		8		const				1	
SIMPLE		parentStore	const	PRIMARY							PRIMARY		8		const				1	
SIMPLE		childStore	index	PRIMARY							protocol        454		NULL				6	Using index
SIMPLE		childNode	ref	PRIMARY,store_id,fk_alf_node_store	                store_id        8		alfrescomgr.childStore.id	162579	
SIMPLE		assoc		ref	parent_node_id,fk_alf_cass_pnode,fk_alf_cass_cnode      fk_alf_cass_cnode 8	        alfrescomgr.childNode.id        1       Using where
                                        ,idx_alf_cass_pri

What's wrong with this query, why does it take so long?
If you have any inputs on how to optimize the query to make it go faster, I'd really appreciate it.

Thanks