Workspace PageTree can become super-slow
The PageTree in workspaces gets a different style for all pages, that contain changes. This is a helpful feature to quickly see, which pages have changes.
But with a larger TYPO3 installation this feature can become unusable and prevent the page tree from loading. This happens because for each page in the page tree and each table in the TCA a MySQL query is executed:
NumberOfPages * NumberOfTablesWithWorkspaceOverlay = NumberOfAdditionalQueries.
We have an installation with 22 Tables with workspace overlay: 10 Core-Tables, 1-Gridelements-Table, 5 Powermail-Tables, 6 Custom-Tables.
A problem might be, that we have ~30.000 content elements and the generated query tries to find which element is changed:
SELECT B.uid as live_uid, B.pid as live_pid, A.uid as offline_uid
FROM tt_content A,tt_content B
WHERE A.pid=-1 AND A.t3ver_wsid=1 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
Links to relevant code positions:
(Even though these links point to the classes for TYPO3 7 instead of 8, I believe the bug will also be present in TYPO3 8, because the queries stay the same)
Currently the page tree does not load at all. There is a timeout after 30 seconds and editors have to hope that the request finishes before. We just removed the workspace hook from
#1 Updated by Simon Dawes over 2 years ago
We have the same issue with workspace page tree generation on a large site in 7.6.18 too.
It is about 4500 live pages and 40,000 content records, and 6 active workspaces.
This worked but slowly in 6.2, with the move to 7.6 it almost always times out now.
Additionally this result then seems to be cached so the user can't see the page tree again unless they log out and back in.
Editors can access/see portions of the tree/pages if you use the Page Filter function at top of tree.
Currently our only solution that works consistently is to modify the fetchPagesWithVersionsInTable function that determines changes to display in
/typo3/sysext/workspaces/Classes/Service/WorkspaceService.php (see attached diff file).
Is this improved in 8.7? If so is there a chance that that improvement can be back ported to 7.6?
If not are the option to have a configuration in 7.6 to switch this function to a simpler version (like we have now) so we don't have to constantly patch the core every update?
#4 Updated by Tobias Gaertner about 1 year ago
In TYPO3 7.6 the core-patch worked for me.
To come around to manual patch the core, for me worked indexing the tt_content table like this:
ALTER TABLE `tt_content` ADD INDEX `pid` (`pid`), ADD INDEX `t3ver_wsid` (`t3ver_wsid`), ADD INDEX `t3ver_state` (`t3ver_state`), ADD INDEX `t3ver_move_id` (`t3ver_move_id`) ;