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)
TIME: 10.424 s
EXPLAIN: 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>
TIME: 0.002 s
EXPLAIN
<pre>
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 26 Using temporary; Using filesort
1 PRIMARY B eq_ref PRIMARY,deleted PRIMARY 4 A.t3ver_oid 1 Using where
2 DERIVED sys_file_reference ref parent,deleted parent 5 653 Using where
3 UNION NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
4 DERIVED sys_file_reference ref parent,deleted parent 5 653 Using where
NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL
</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.