Bug #80898
closed
No page tree in a workspaces
100%
Description
I just debugged a problem that the page tree was not shown when in a workspaces. The AJAX request actually timed out.
I finally nailed it down to method \TYPO3\CMS\Workspaces\Service\WorkspaceService::fetchPagesWithVersionsInTable and its query for tableName sys_file_reference.
This is the culprit query:
SELECT `B`.`pid` AS `pageId` FROM `sys_file_reference` `A`, `sys_file_reference` `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)) GROUP BY `pageId`
It takes 131 seconds to execute on my table having 53k rows and returns 0 rows.
Query rewritten:
SELECT
B.pid AS pageId
FROM
sys_file_reference A,
sys_file_reference 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
GROUP BY pageId
The "GROUP BY" is the part killing everything. From what I understand, it looks like it is used to remove duplicates in the result set. If that's really the case, then using a "DISTINCT" instead of a "GROUP BY" effectively drops the execution time from 131 sec. to around 230 ms.
Files
Updated by Xavier Perseguers over 7 years ago
I just don't know how to use "DISTINCT" instead of "GROUP BY" with Doctrine DBAL.
Updated by Oliver Hader over 7 years ago
- Status changed from New to Accepted
GROUP BY
+----+-------------+-------+------+------------------------+---------+---------+-------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+------------------------+---------+---------+-------------+------+----------------------------------------------+ | 1 | SIMPLE | A | ref | parent,deleted | parent | 6 | const,const | 1 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | B | ref | PRIMARY,parent,deleted | deleted | 2 | const | 83 | Using index condition; Using where | +----+-------------+-------+------+------------------------+---------+---------+-------------+------+----------------------------------------------+
DISTINCT
+----+-------------+-------+------+------------------------+---------+---------+-------------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+------------------------+---------+---------+-------------+------+------------------------------------+ | 1 | SIMPLE | A | ref | parent,deleted | parent | 6 | const,const | 1 | Using where; Using temporary | | 1 | SIMPLE | B | ref | PRIMARY,parent,deleted | deleted | 2 | const | 83 | Using index condition; Using where | +----+-------------+-------+------+------------------------+---------+---------+-------------+------+------------------------------------+
Updated by Oliver Hader over 7 years ago
Thus, using DISTINCT removes the filesort on the temporary joined table.
Updated by Gerrit Code Review over 7 years ago
- Status changed from Accepted 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/52506
Updated by Gerrit Code Review over 7 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/52506
Updated by Jens Jacobsen over 7 years ago
This problem also applies to TYPO3 7.6.18 LTS where this query is used:
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=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 GROUP BY live_pid;
I also see a problem with the tt_content query. This also seem to increase in duration in relation to the table size:
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 GROUP BY live_pid;
Do we need a seperate Issue for 7.6 LTS ?
Updated by Xavier Perseguers over 7 years ago
Thanks Jens. I expected it to be true for 7.6 as well but did not test yet. Please go to https://review.typo3.org/52506 and write a comment for the commit message so that branch "7.6" is targeted as well.
Updated by Jens Jacobsen over 7 years ago
Xavier Perseguers wrote:
Thanks Jens. I expected it to be true for 7.6 as well but did not test yet. Please go to https://review.typo3.org/52506 and write a comment for the commit message so that branch "7.6" is targeted as well.
I don't think its that easy because the code for 7.6 is totally different or am I totally wrong about it?
Updated by Jens Jacobsen over 7 years ago
- File original_query.png original_query.png added
- File modified_query.png modified_query.png added
For 7.6.18 LTS a solution could be to add "ORDER BY NULL" for the query in typo3/sysext/workspaces/Classes/Service/WorkspaceService.php:752
Updated by Xavier Perseguers over 7 years ago
- The target of the patch is not whether the "very same" patch should be applied to another branch but whether the same problem (and fix) - generally speaking - is valjd for another branch
ORDER BY NULLseems to be very MySQL specific
Updated by Oliver Hader over 7 years ago
The usage of DISTINCT (or any other statement) has to be checked for other DBMS as well (thus, not only for MySQL).
Besides that, the code seems to be wrong in general which was basically caused by the Doctrine DBAL migration in https://review.typo3.org/#/c/48600/ - $pageIds[$row['uid']] = $row; does not make sense, since uid is not part of the selected columns.
Updated by Oliver Hader over 7 years ago
- Related to Task #76626: Doctrine: migrate ext:workspace added
Updated by Gerrit Code Review over 7 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/52506
Updated by Kevin Quiatkowski over 7 years ago
How is the Status for 7.6.18? I can reproduce it, and the ORDER BY NULL increases the performance. Maybe there is a better Solution. But better get this dirty one than nothing. How to get it in the 7.6 branch?
Updated by Gerrit Code Review over 7 years ago
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/52853
Updated by Oliver Hader over 7 years ago
- Status changed from Under Review to Resolved
- % Done changed from 0 to 100
Applied in changeset 4d056965fddd3768d669b7bdcd0a039a3c1ea1ba.
Updated by
Updated by Sascha Schieferdecker over 6 years ago
Hi, I'd like to have this bug reopenend. We recently updated a TYPO3 Instance to 8.7.13 with around 40 Workspaces. The patch splits the query into subqueries, but in the end also does a GROUP BY. This led to queries running for 300 seconds and longer. I hotfixed it by using the DISTINCT Variant of Patchset 1, this works without problems.
Updated by Marina Bey over 6 years ago
Hi, we have the same problem in TYPO3 8. Question to Sascha or anyone else: would you please post your solution with the DISTINCT-part? Thanks a lot!
Updated by 
Updated by Oliver Hader about 5 years ago
- Related to Bug #86945: Performance PageTree in Workspace added