Details
-
Bug
-
Resolution: Fixed
-
High
-
5.2, 5.3, 5.4-dev
-
None
-
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
- relates to
-
EZP-23140 Query optimizer
- Open