Bug #86945
Updated by Alexander Opitz over 5 years ago
All nodes of a PageTree will call TYPO3\CMS\Workspaces\Service\WorkspaceService::hasPageRecordVersions($workspaceId, $pageId) which themself calls TYPO3\CMS\Workspaces\Service\WorkspaceService::fetchPagesWithVersionsInTable($workspaceId, $tableName); for every versionised table. This function leads to a SQL STatement, whcih will get very slow on large tables. SQL Statement: <pre><code class="sql"> SELECT `B`.`pid` AS `pageId` FROM `sys_file_reference` `B` WHERE ( (`B`.`uid` IN ( SELECT `A`.`t3ver_oid` FROM `sys_file_reference` `A` WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 14) AND (`A`.`t3ver_state` <> 4) AND (`A`.`deleted` = 0))) OR (`B`.`t3ver_move_id` IN ( SELECT `A`.`t3ver_oid` FROM `sys_file_reference` `A` WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 14) AND (`A`.`t3ver_state` = 4) AND (`A`.`deleted` = 0)))) AND (`B`.`deleted` = 0) GROUP BY `B`.`pid` </code></pre> The problem is the OR between the two IN with SQL Subselect, cause this leads in MySQL/MariaDB to processing the OUTER SQL before the INNER SQL. (IMHO in MariaDB this issue is tried to address) EXPLAIN <pre> id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY B index deleted parent 5 NULL 11273 Using where 3 DEPENDENT SUBQUERY A ref parent,deleted parent 5 const,const 653 Using where 2 DEPENDENT SUBQUERY A ref parent,deleted parent 5 const,const 653 Using where </pre> Which needs around 10 seconds on demo system. Better use JOIN Statement: <pre><code class="sql"> SELECT `B`.`pid` AS `pageId` FROM `sys_file_reference` `B` JOIN (SELECT `t3ver_oid` FROM `sys_file_reference` WHERE (`pid` = -1) AND (`t3ver_wsid` = 14) AND (`t3ver_state` <> 4) AND (`deleted` = 0)) `A` ON `B`.`uid` = `A`.`t3ver_oid` WHERE (`B`.`deleted` = 0) GROUP BY `B`.`pid` UNION SELECT `B`.`pid` AS `pageId` FROM `sys_file_reference` `B` JOIN (SELECT `t3ver_oid` FROM `sys_file_reference` WHERE (`pid` = -1) AND (`t3ver_wsid` = 14) AND (`t3ver_state` = 4) AND (`deleted` = 0)) `C` ON `B`.`t3ver_move_id` = `C`.`t3ver_oid` WHERE (`B`.`deleted` = 0) GROUP BY `B`.`pid` </code></pre> Using UNION to merge both results (like OR) together. But Union is not possible with QueryBuilder but IMHO it would be sufficient in speed to get the data with 2 SQL statements. This issue happened on a larger custom system after migrating from 7LTS to 8LTS, will check what changed and what may help.