Actions
Bug #86945
closedEpic #88474: Page tree performance in 9.5
Performance PageTree in Workspace
Start date:
2018-11-16
Due date:
% Done:
100%
Estimated time:
TYPO3 Version:
8
PHP Version:
Tags:
Complexity:
hard
Is Regression:
Sprint Focus:
Description
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:
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`
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:
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
Which needs around 10 seconds on demo system.
Better use JOIN Statement:
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`
TIME: 0.002 s
EXPLAIN
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
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.
Actions