Hi,
we are using the eZ Publish CMS which has a very flexible content model, but produces monster queries as the one below.
SELECT DISTINCT ezcontentobject., ezcontentobject_tree., ezcontentclass.serialized_name_list as class_serialized_name_list, ezcontentclass.identifier as class_identifier, ezcontentclass.is_container as is_container , ezcontentobject_name.name as name, ezcontentobject_name.real_translation FROM ezcontentobject_tree, ezcontentobject,ezcontentclass , ezcontentobject_name , ezcontentobject_attribute as myfilter_alias WHERE ezcontentobject_tree.path_string like ‘/1/2/781/%’ and ezcontentobject_tree.contentobject_id = myfilter_alias.contentobject_id AND ezcontentobject_tree.contentobject_version = myfilter_alias.version AND ( ( myfilter_alias.contentclassattribute_id = 500 AND myfilter_alias.data_int != 1 ) OR ezcontentobject.contentclass_id != 56 ) AND ezcontentclass.version=0 AND ezcontentobject_tree.node_id != 781 AND ezcontentobject_tree.contentobject_id = ezcontentobject.id AND ezcontentclass.id = ezcontentobject.contentclass_id AND ezcontentobject.contentclass_id IN ( 56, 57, 49 ) 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 (1, 15, 16, 2, 7, 8, 9)) OR (ezcontentobject.contentclass_id in (29, 30, 31, 32, 33, 40) AND ezcontentobject.section_id in (3)) OR (ezcontentobject.section_id in (14))) AND ezcontentobject.language_mask & 3 > 0 ORDER BY ezcontentobject.published DESCLIMIT 0, 10
The query was very fast, if there wasn’t the ORDER clause, so I tried to optimize that. Increasing sort buffers didn’t help, as there are probably just to many tables and rows involved. I’ve ended up removing the ORDER clause in the main query, and adding a subquery in the FROM clause which is sorted and then joined with the other tables. Now the query looks like this:
SELECT DISTINCT ezcontentobject., ezcontentobject_tree., ezcontentclass.serialized_name_list as class_serialized_name_list, ezcontentclass.identifier as class_identifier, ezcontentclass.is_container as is_container , ezcontentobject_name.name as name, ezcontentobject_name.real_translation FROM ezcontentobject_tree, ezcontentobject,ezcontentclass , ezcontentobject_name , ezcontentobject_attribute as myfilter_alias , ( select id from ezcontentobject where ezcontentobject.contentclass_id IN ( 56, 57, 49 ) order by published desc ) as ezcobj WHERE ezcobj.id = ezcontentobject.id and ezcontentobject_tree.path_string like ‘/1/2/781/%’ and ezcontentobject_tree.contentobject_id = myfilter_alias.contentobject_id AND ezcontentobject_tree.contentobject_version = myfilter_alias.version AND ( ( myfilter_alias.contentclassattribute_id = 500 AND myfilter_alias.data_int != 1 ) OR ezcontentobject.contentclass_id != 56 ) AND ezcontentclass.version=0 AND ezcontentobject_tree.node_id != 781 AND ezcontentobject_tree.contentobject_id = ezcontentobject.id AND ezcontentclass.id = ezcontentobject.contentclass_id AND ezcontentobject.contentclass_id IN ( 56, 57, 49 ) 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 (1, 15, 16, 2, 7, 8, 9)) OR (ezcontentobject.contentclass_id in (29, 30, 31, 32, 33, 40) AND ezcontentobject.section_id in (3)) OR (ezcontentobject.section_id in (14))) AND ezcontentobject.language_mask & 3 > 0 LIMIT 0, 10
This gives about a 20-fold performance increase on an idle server and a over 50-fold increase on a busy server. Sorting also works, but I couldn’t find anything about sorting subqueries in the FROM clause in the MySQL Manual (link).
So my question is, is this just working by accident or is this a common way to speed up sorting in such complex queries?
Thanks
Erwin