Details
-
Bug
-
Resolution: Unresolved
-
High
-
5.4.10
-
None
Description
The customer experienced extremally long query execution time after upgrade from MySQL 5.5 to MySQL 5.6.37.
The query is generated by fetch() legacy method.
fetch( 'content', 'tree', hash( 'parent_node_id', 63, 'class_filter_type', 'include', 'class_filter_array',array( 'pc_article' ), 'main_node_only', true(), 'sort_by', array( 'attribute', false(), 'pc_article/publish_date' ), 'limit', 5 ) )}
Query provided by the customer:
SELECT DISTINCT ezcontentobject.contentclass_id
FROM ezcontentobject_tree
INNER JOIN ezcontentobject ON (ezcontentobject_tree.contentobject_id = ezcontentobject.id)
INNER JOIN ezcontentclass ON (ezcontentclass.version = 0 AND ezcontentclass.id = ezcontentobject.contentclass_id)
INNER JOIN ezcontentobject_attribute a0 ON ( a0.contentobject_id = ezcontentobject.id AND a0.contentclassattribute_id=747
AND a0.version=ezcontentobject.current_version )
WHERE ezcontentobject_tree.path_string like '/1/2/60/63/%'
AND ezcontentobject_tree.node_id != 63
AND ezcontentobject_tree.node_id = ezcontentobject_tree.main_node_id
AND ezcontentobject.contentclass_id IN ( 17 )
AND ezcontentobject.language_mask & 3 > 0
ORDER BY a0.sort_key_int DESC LIMIT 0, 5;
Query generated internally by eZContentObjectTreeNode::subTreeByNodeID:
SELECT DISTINCT ezcontentobject.contentclass_id,
ezcontentobject.current_version,
ezcontentobject.id,
ezcontentobject.initial_language_id,
ezcontentobject.language_mask,
ezcontentobject.modified,
ezcontentobject.owner_id,
ezcontentobject.published,
ezcontentobject.remote_id AS object_remote_id,
ezcontentobject.section_id,
ezcontentobject.status,
ezcontentobject_tree.contentobject_is_published,
ezcontentobject_tree.contentobject_version,
ezcontentobject_tree.depth,
ezcontentobject_tree.is_hidden,
ezcontentobject_tree.is_invisible,
ezcontentobject_tree.main_node_id,
ezcontentobject_tree.modified_subnode,
ezcontentobject_tree.node_id,
ezcontentobject_tree.parent_node_id,
ezcontentobject_tree.path_identification_string,
ezcontentobject_tree.path_string,
ezcontentobject_tree.priority,
ezcontentobject_tree.remote_id,
ezcontentobject_tree.sort_field,
ezcontentobject_tree.sort_order,
ezcontentclass.serialized_name_list AS
class_serialized_name_list,
ezcontentclass.identifier AS class_identifier,
ezcontentclass.is_container AS is_container,
ezcontentobject_name.NAME,
ezcontentobject_name.real_translation,
a0.sort_key_int
FROM ezcontentobject_tree
INNER JOIN ezcontentobject
ON ( ezcontentobject_tree.contentobject_id = ezcontentobject.id )
INNER JOIN ezcontentclass
ON ( ezcontentclass.version = 0
AND ezcontentclass.id = ezcontentobject.contentclass_id )
INNER JOIN ezcontentobject_name
ON ( ezcontentobject_tree.contentobject_id =
ezcontentobject_name.contentobject_id
AND ezcontentobject_tree.contentobject_version =
ezcontentobject_name.content_version )
INNER JOIN ezcontentobject_attribute a0
ON ( a0.contentobject_id = ezcontentobject.id
AND a0.contentclassattribute_id = 747
AND a0.version = ezcontentobject.current_version
AND ( a0.language_id & ezcontentobject.language_mask > 0
AND ( ( ezcontentobject.language_mask - (
ezcontentobject.language_mask &
a0.language_id
) ) & 1 ) + ((
(
ezcontentobject.language_mask - (
ezcontentobject.language_mask & a0.language_id
) ) & 2 )) <
( a0.language_id & 1 ) + (( a0.language_id & 2 ))
) )
WHERE ezcontentobject_tree.path_string LIKE '/1/2/60/63/%'
AND ezcontentobject_tree.node_id != 63
AND ezcontentobject_tree.node_id = ezcontentobject_tree.main_node_id
AND ezcontentobject.contentclass_id IN ( 17 )
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.language_mask & 3 > 0
ORDER BY a0.sort_key_int DESC;
Issue confirmed in MySQL 5.6.37 and MariaDB 10.2.8.
A slightly better execution time was noticed using MariaDB 10.1.26.
MySQL 5.7 returns an exception:
ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'plein.a0.sort_key_int' which is not in SELECT list; this is incompatible with DISTINCT