Details
-
Bug
-
Resolution: Fixed
-
High
-
5.3.3, 5.4.0
Description
With the following:
- 18 languages
- 29 attributes
Loading the content will return about 160 000 rows:
- language * location (18 * 1)
- language * name (join on ezcontentobject_name) (18 * 1)
- language * attributes (18 * 29)
That's a tad much. Yes, language filtering should be used whenever possible, but it's still a lot.
This causes following issues:
- breaks memory limits
- big amounts of data needed to be transferred from mysql to PHP
- very slow
things that could be improved
The location join should not return more than 1 row. We might also want to exclude secondary locations, as they shouldn't matter when loading a Content.
The name join shouldn't return more than languages rows
In total, this query should return languages * attributes rows, not more (522 in that case).
Query example
SELECT `ezcontentobject`.`id` `ezcontentobject_id`, `ezcontentobject`.`contentclass_id` `ezcontentobject_contentclass_id`, `ezcontentobject`.`section_id` `ezcontentobject_section_id`, `ezcontentobject`.`owner_id` `ezcontentobject_owner_id`, `ezcontentobject`.`remote_id` `ezcontentobject_remote_id`, `ezcontentobject`.`current_version` `ezcontentobject_current_version`, `ezcontentobject`.`initial_language_id` `ezcontentobject_initial_language_id`, `ezcontentobject`.`modified` `ezcontentobject_modified`, `ezcontentobject`.`published` `ezcontentobject_published`, `ezcontentobject`.`status` `ezcontentobject_status`, `ezcontentobject`.`name` `ezcontentobject_name`, `ezcontentobject`.`language_mask` `ezcontentobject_language_mask`, `ezcontentobject_version`.`id` `ezcontentobject_version_id`, `ezcontentobject_version`.`version` `ezcontentobject_version_version`, `ezcontentobject_version`.`modified` `ezcontentobject_version_modified`, `ezcontentobject_version`.`creator_id` `ezcontentobject_version_creator_id`, `ezcontentobject_version`.`created` `ezcontentobject_version_created`, `ezcontentobject_version`.`status` `ezcontentobject_version_status`, `ezcontentobject_version`.`contentobject_id` `ezcontentobject_version_contentobject_id`, `ezcontentobject_version`.`language_mask` `ezcontentobject_version_language_mask`, `ezcontentobject_version`.`initial_language_id` `ezcontentobject_version_initial_language_id`, `ezcontentobject_attribute`.`id` `ezcontentobject_attribute_id`, `ezcontentobject_attribute`.`contentclassattribute_id` `ezcontentobject_attribute_contentclassattribute_id`, `ezcontentobject_attribute`.`data_type_string` `ezcontentobject_attribute_data_type_string`, `ezcontentobject_attribute`.`language_code` `ezcontentobject_attribute_language_code`, `ezcontentobject_attribute`.`language_id` `ezcontentobject_attribute_language_id`, `ezcontentobject_attribute`.`version` `ezcontentobject_attribute_version`, `ezcontentobject_attribute`.`data_float` `ezcontentobject_attribute_data_float`, `ezcontentobject_attribute`.`data_int` `ezcontentobject_attribute_data_int`, `ezcontentobject_attribute`.`data_text` `ezcontentobject_attribute_data_text`, `ezcontentobject_attribute`.`sort_key_int` `ezcontentobject_attribute_sort_key_int`, `ezcontentobject_attribute`.`sort_key_string` `ezcontentobject_attribute_sort_key_string`, `ezcontentobject_name`.`name` `ezcontentobject_name_name`, `ezcontentobject_name`.`content_translation` `ezcontentobject_name_content_translation`, `ezcontentobject_tree`.`main_node_id` `ezcontentobject_tree_main_node_id` FROM `ezcontentobject` -- nb results: 1 LEFT JOIN `ezcontentobject_version` ON `ezcontentobject_version`.`contentobject_id` = `ezcontentobject`.`id` -- nb results: 522 LEFT JOIN `ezcontentobject_attribute` ON (`ezcontentobject_attribute`.`contentobject_id` = `ezcontentobject_version`.`contentobject_id` AND `ezcontentobject_attribute`.`version` = `ezcontentobject_version`.`version`) -- nb results: 18 LEFT JOIN `ezcontentobject_tree` ON `ezcontentobject_tree`.`contentobject_id` = `ezcontentobject_version`.`contentobject_id` -- nb results: 18 LEFT JOIN `ezcontentobject_name` ON (`ezcontentobject_name`.`contentobject_id` = `ezcontentobject_version`.`contentobject_id` AND `ezcontentobject_name`.`content_version` = `ezcontentobject_version`.`version`) WHERE (`ezcontentobject`.`id` = 3886 AND `ezcontentobject_version`.`version` = 171)