Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Speeding up sort with subquery?

erwinerwin EntrantInactive User Role Beginner
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</pre>


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</pre>


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

Comments

  • xaprbxaprb Mentor Inactive User Role Beginner
    It's not an accident, and I have used that kind of technique before. Those queries look horrible, I don't envy you.
  • erwinerwin Entrant Inactive User Role Beginner
    Yes they are horrible and they're sometimes horribly slow, but now I'm confident that I'll be able to speed up some of the most time consuming queries.

    Thanks a lot for your answer !!
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.