When issuing a fetch like this:

{def $children=fetch(
	'content',
	'list',
	hash(
		'parent_node_id', $node.parent_node_id,
		'sort_by', array(
			array( 'attribute', false(), concat($node.class_identifier, '/date') ),
			array( 'published', false() )
		),
		'limit', 5
	) 
)}

the resulting query can take pretty much time to execute when there are a lot of objects in queried subtree. A special multikey index on ezcontentobject_attribute can help reduce the resultset dramatically:

CREATE INDEX ezcontentobject_attribute_co_id_cattr_id_ver ON ezcontentobject_attribute (contentobject_id, contentclassattribute_id, version);

After isolating and anylyzing the resulting queries I found out, that custom sorting (as seen above) will almost always result in a 'filesort' which is a total performance killer. By applying the above index you won't avoid that, but at least you can reduce JOIN-time and the resultsets cardinality, thus reducing execution time by 20% to 50%