GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Speeding up sort with subquery?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Speeding up sort with subquery?

    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

  • #2
    It's not an accident, and I have used that kind of technique before. Those queries look horrible, I don't envy you.

    Comment


    • #3
      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 !!

      Comment

      Working...
      X