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

Slow MySQL queries when using Content Search with sortClauses

    XMLWordPrintable

    Details

    • Sprint:
      Pollux Core S5

      Description

      On installations with a large number of records, Content Search operations that use sortClauses can take a long time. For instance, consider the following scenario:

      ezcontentobject_attribute: 2441035
      ezcontentobject_version: 109905
      ezcontentobject: 31167
      

      The following Content Search API call will take 30-40 seconds to generate the corresponding MySQL queries:

      $query = new Query();
      $query->limit = 7;
      $query->sortClauses = new Query\SortClause\Field('<my_class_identifier>', '<my_field>', Query::SORT_DESC, 'ger-AT');
      
      $repository = $this->getContainer()->get( 'ezpublish.api.repository' );
      $searchService = $repository->getSearchService();
      $resultset = $searchService->findContent($query);
      foreach ($resultset->searchHits as $hit) {
      		$results[] = $hit->valueObject;
      }
      
      

      The equivalent Location Search API call will take about 3 seconds:

      $locationQuery = new LocationQuery();
      $locationQuery->limit = 7;
      $locationQuery->sortClauses = new Query\SortClause\Field('<my_class_identifier>', '<my_field>', Query::SORT_DESC, 'ger-AT');
      
      $repository = $this->getContainer()->get( 'ezpublish.api.repository' );
      $searchService = $repository->getSearchService();
      $contentService = $repository->getContentService();
      $resultset = $searchService->findLocations($locationQuery);
      foreach ($resultset->searchHits as $hit) {
      	$results[] = $contentService->loadContent($hit->valueObject->contentInfo->id);
      }
      

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            nuno.oliveira-obsolete@ez.no Nuno Oliveira (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 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 - 4 hours, 40 minutes
                4h 40m