Details
-
Improvement
-
Resolution: Fixed
-
Medium
-
3.10.0, 3.9.4, 4.0.0alpha2
-
None
Description
The mysql query generate by a fetch content list are not optimize.
The query use the path_string to find the childrens nodes.
The query could be 10 time faster if it use the parent_node_id to find the childrens nodes
this fetch
{def $nodes=fetch( 'content', 'list', hash( 'parent_node_id', 14766 ,
'sort_by', array( 'priority', true() ),
'attribute_filter', array( array( 'priority', '>', '0' ))) )}
Will generate this query
SELECT ezcontentobject.*,
ezcontentobject_tree.*,
ezcontentclass.serialized_name_list as class_serialized_name_list,
ezcontentclass.identifier as class_identifier
, ezcontentobject_name.name as name, ezcontentobject_name.real_translation
FROM
ezcontentobject_tree,
ezcontentobject,ezcontentclass
, ezcontentobject_name
WHERE
ezcontentobject_tree.path_string like '/1/2/14766/%'
and ezcontentobject_tree.depth <= 3
and ( ezcontentobject_tree.priority > '0' )
AND ezcontentclass.version=0
AND ezcontentobject_tree.node_id != 14766
AND ezcontentobject_tree.contentobject_id = ezcontentobject.id
AND ezcontentclass.id = ezcontentobject.contentclass_id AND
ezcontentobject_tree.contentobject_id = ezcontentobject_name.contentobject_id and
ezcontentobject_tree.contentobject_version = ezcontentobject_name.content_version and
( ezcontentobject_name.language_id & ezcontentobject.language_mask > 0 AND
( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 1 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 2 ) )
<
( ezcontentobject_name.language_id & 1 )
+ ( ( ezcontentobject_name.language_id & 2 ) )
)
AND ezcontentobject_tree.is_invisible = 0
AND ((ezcontentobject.section_id in ( 23, 24, 6, 7, 8, 9)))
AND ezcontentobject.language_mask & 3 > 0
ORDER BY ezcontentobject_tree.priority ASC
The mysql query should use the columns parent_node_id instead of the .path_string.
The query should be
SELECT ezcontentobject.*,
ezcontentobject_tree.*,
ezcontentclass.serialized_name_list as class_serialized_name_list,
ezcontentclass.identifier as class_identifier
, ezcontentobject_name.name as name, ezcontentobject_name.real_translation
FROM
ezcontentobject_tree,
ezcontentobject,ezcontentclass
, ezcontentobject_name
WHERE
ezcontentobject_tree.parent_node_id =14766
and ( ezcontentobject_tree.priority > '0' )
AND ezcontentclass.version=0
AND ezcontentobject_tree.contentobject_id = ezcontentobject.id
AND ezcontentclass.id = ezcontentobject.contentclass_id
AND ezcontentobject_tree.contentobject_id = ezcontentobject_name.contentobject_id and
ezcontentobject_tree.contentobject_version = ezcontentobject_name.content_version and
( ezcontentobject_name.language_id & ezcontentobject.language_mask > 0 AND
( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 1 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 2 ) )
<
( ezcontentobject_name.language_id & 1 )
+ ( ( ezcontentobject_name.language_id & 2 ) )
)
AND ezcontentobject_tree.is_invisible = 0
AND ((ezcontentobject.section_id in ( 23, 24, 6, 7, 8, 9)))
AND ezcontentobject.language_mask & 3 > 0
ORDER BY ezcontentobject_tree.priority ASC
