Bug #86945
closedEpic #88474: Page tree performance in 9.5
Performance PageTree in Workspace
100%
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.
Updated by Alexander Opitz about 6 years ago
TYPO3 7 LTS
SELECT `B`.`uid` as `live_uid`, `B`.`pid` as `live_pid`, `A`.`uid` as `offline_uid`
FROM `sys_file_reference` `A`,`sys_file_reference` `B`
WHERE
`A`.`pid`=-1 AND `A`.`t3ver_wsid`= 14 AND (`A`.`t3ver_oid` = `B`.`uid` AND `A`.`t3ver_state` <> 4
OR
`A`.`t3ver_oid` = `B`.`t3ver_move_id` AND `A`.`t3ver_state` = 4) AND `A`.`deleted` = 0 AND `B`.`deleted` = 0 GROUP BY `live_pid`
TIME: 0.047 s
EXPLAIN
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE A ref parent,deleted parent 5 const,const 653 Using where; Using temporary; Using filesort 1 SIMPLE B ALL PRIMARY,deleted NULL NULL NULL 11154 Using where; Using join buffer
Updated by Gerrit Code Review about 6 years ago
- Status changed from New to Under Review
Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/59037
Updated by Gerrit Code Review about 6 years ago
Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/59037
Updated by Gerrit Code Review about 6 years ago
Patch set 3 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/59037
Updated by Gerrit Code Review about 6 years ago
Patch set 4 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/59037
Updated by Gerrit Code Review over 5 years ago
Patch set 1 for branch 9.5 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/61604
Updated by Alexander Opitz over 5 years ago
- Status changed from Under Review to Resolved
- % Done changed from 0 to 100
Applied in changeset 98c74eb212b728db2818c256994c501a6173ab7b.
Updated by Gerrit Code Review over 5 years ago
- Status changed from Resolved to Under Review
Patch set 1 for branch TYPO3_8-7 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/61605
Updated by Alexander Opitz over 5 years ago
- Status changed from Under Review to Resolved
Applied in changeset 0dab9b4183208040c76e84ea845396b742d91fab.
Updated by Oliver Hader over 5 years ago
- Related to Bug #89464: PageTree in workspace not loading added
Updated by Oliver Hader over 5 years ago
Alexander Opitz wrote:
TYPO3 7 LTS
[...]
TIME: 0.047 s
EXPLAIN[...]
Actually this looks like the code that was used earlier which lead to timeouts, see https://review.typo3.org/c/Packages/TYPO3.CMS/+/52853
Updated by Oliver Hader over 5 years ago
- Related to Bug #80898: No page tree in a workspaces added
Updated by Alexander Opitz over 5 years ago
Added a final note here https://forge.typo3.org/issues/89464#note-20