Is there an efficient way to get a count of all descendant nodes of a given node type?

[ma6rl]

I need to get a count of all descendent nodes of a parent node that match a given node type. Currently I have 13000 dependent nodes (they are not direct children) and am determining the count by running the following query:

SELECT node.* FROM [app:asset] AS node WHERE ISDESCENDANTNODE('/organizations/default/assets') ORDER by node.[jcr:created] DESC

I do have indexing enabled and see the following index used:

Index [node] <INDEX_SPECIFICATION=DescendantsByPath, provider=<implicit>, cost~=1000, cardinality~=10000, selectivity~=?, constraints=[ISDESCENDANTNODE(node,'/organizations/default/assets')], path=/organizations/default/assets, INDEX_USED=true>

The problem is the query is taking over 10 seconds to return the count, if I double the number of nodes to 26000 it takes 20 seconds. Is there a more efficient way to do this? I see there is a Child Count operand but it appears this only works on direct children of the node.

[hchiorean]

Indexes can't handle these type of constraints, so defining/not defining indexes will not make a difference. These type of join-constraints are always handled internally. The only thing that you could improve with your query is not select * but only a simple property (e.g. node.[jcr:name]) since you're only interested in the count.

Apart from this you should try to profile the query and investigate where time is being spent. Using certain JCR features (most notably ACLs) is known to reduce the throughput of queries because each result has to be checked for permissions against the ACL manager.