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)
