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

    • 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

        Activity

          People

            Unassigned Unassigned
            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