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