Uploaded image for project: 'eZ Publish / Platform'
  1. eZ Publish / Platform
  2. EZP-23037

Subtree criterion in content search does not scale (MySQL timeouts)

    XMLWordPrintable

    Details

    • Sprint:
      Castor Core S1, Castor Core S2

      Description

      Each subtree criterion in a content search query creates a JOIN with the ezcontentobject_tree table.

      The issue is that this criterion is used for user roles/permissions in policy and role assignment subtree limitations.

      Given enough limitations (which cause a significant amount of joins), and content with multiple locations, even a simple query ( using ParentLocationId and ContentTypeIdentifier criterions, for example) may become extremely slow, or even timeout:

              $query = new Query();
              $query->sortClauses[] = new Query\SortClause\LocationPriority( Query::SORT_DESC );
              $query->criterion = new Query\Criterion\LogicalAnd(
                  array
                  (
                      new Query\Criterion\ParentLocationId( $parentLocationId ),
                      new Query\Criterion\ContentTypeIdentifier( $contentTypeIdentifier ),
                  )
              );
              return $searchService->findContent( $query );
      

      Sample of a query with a few limitations:

      SELECT
        `ezcontentobject`.`id`,
        `sort_table_0`.`priority` AS `sort_column_0` 
      FROM `ezcontentobject` 
      INNER JOIN  ezcontentobject_version ON ezcontentobject.id = ezcontentobject_version.contentobject_id 
      LEFT JOIN  `ezcontentobject_tree` AS `sort_table_0` ON `sort_table_0`.`contentobject_id` = `ezcontentobject`.`id` 
      LEFT JOIN `ezcontentobject_tree` AS `53a1c75ec0362` ON `53a1c75ec0362`.`contentobject_id` = `ezcontentobject`.`id` 
      LEFT JOIN `ezcontentobject_tree` AS `53a1c75ec04be` ON `53a1c75ec04be`.`contentobject_id` = `ezcontentobject`.`id` 
      LEFT JOIN `ezcontentobject_tree` AS `53a1c75ec0632` ON `53a1c75ec0632`.`contentobject_id` = `ezcontentobject`.`id` 
      LEFT JOIN `ezcontentobject_tree` AS `53a1c75ec08e2` ON `53a1c75ec08e2`.`contentobject_id` = `ezcontentobject`.`id` 
      LEFT JOIN `ezcontentobject_tree` AS `53a1c75ec0a1c` ON `53a1c75ec0a1c`.`contentobject_id` = `ezcontentobject`.`id` 
      LEFT JOIN `ezcontentobject_tree` AS `53a1c75ec0b76` ON `53a1c75ec0b76`.`contentobject_id` = `ezcontentobject`.`id` 
      LEFT JOIN `ezcontentobject_tree` AS `53a1c75ec0cd9` ON `53a1c75ec0cd9`.`contentobject_id` = `ezcontentobject`.`id` 
      WHERE
      ...
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                joao.inacio-obsolete@ez.no Joao Inacio (Inactive)
              • Votes:
                2 Vote for this issue
                Watchers:
                12 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 2 days, 1 hour, 25 minutes
                  2d 1h 25m