AnsweredAssumed Answered

Bad performance because of a slow query

Question asked by asterixko on Nov 14, 2012
We've an environment with Alfresco Community 4.0.0 (4003) with a huge amount of contents but only a set of users.  The environment is in charge to distribute content to third-party systems using CMIS protocol and is using MySQL as the database server.

Everything is running perfectly except when alfresco is performing the next query to the database:

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 = 805
        order by
   assoc.assoc_index ASC,
   assoc.id ASC

This query retrieves all children for the node placed in the next path: "   sys:system/sys:people/cm:guest/app:configurations". This specific node has more than 100000 children nodes, all of them are from the type "cm:object" and the children name is always "preferences". This query is very slow because MySQL has to explore more than 500000 rows to get the results.

We guess that disabling guest access we could avoid that alfresco would perform this query. But after doing it we've reduced the amount of times that this query is performed, but it is still launched by alfresco (more than 50 times per day).

We would like you to help us about: How can we avoid the execution of this query by alfresco? What is the goal of this query and why is being launched?

Outcomes