Bug #80898

No page tree in a workspaces

Added by Xavier Perseguers over 4 years ago. Updated almost 3 years ago.

Status:
Closed
Priority:
Must have
Category:
Workspaces
Target version:
Start date:
2017-04-19
Due date:
% Done:

100%

Estimated time:
TYPO3 Version:
8
PHP Version:
7.0
Tags:
Complexity:
Is Regression:
Sprint Focus:

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

original_query.png (59.8 KB) original_query.png original query Jens Jacobsen, 2017-04-20 11:10
modified_query.png (58.1 KB) modified_query.png modified query Jens Jacobsen, 2017-04-20 11:10

Related issues

Related to TYPO3 Core - Task #76626: Doctrine: migrate ext:workspaceClosedEugene Kenah Djomo2016-06-14

Actions
Related to TYPO3 Core - Bug #86945: Performance PageTree in WorkspaceClosedAlexander Opitz2018-11-16

Actions
#1

Updated by Xavier Perseguers over 4 years ago

I just don't know how to use "DISTINCT" instead of "GROUP BY" with Doctrine DBAL.

#2

Updated by Oliver Hader over 4 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 |
+----+-------------+-------+------+------------------------+---------+---------+-------------+------+------------------------------------+
#3

Updated by Oliver Hader over 4 years ago

Thus, using DISTINCT removes the filesort on the temporary joined table.

#4

Updated by Gerrit Code Review over 4 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

#5

Updated by Gerrit Code Review over 4 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

#6

Updated by Jens Jacobsen over 4 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 ?

#7

Updated by Xavier Perseguers over 4 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.

#8

Updated by Jens Jacobsen over 4 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?

#9

Updated by Jens Jacobsen over 4 years ago

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

#10

Updated by Xavier Perseguers over 4 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 NULL seems to be very MySQL specific
#11

Updated by Oliver Hader over 4 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.

#12

Updated by Oliver Hader over 4 years ago

  • Related to Task #76626: Doctrine: migrate ext:workspace added
#13

Updated by Gerrit Code Review over 4 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

#14

Updated by Kevin Quiatkowski over 4 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?

#15

Updated by Gerrit Code Review over 4 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

#16

Updated by Oliver Hader over 4 years ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100
#17

Updated by Rafal Brzeski about 4 years ago

No fix for 7.6 ? :(

#18

Updated by Sascha Schieferdecker over 3 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.

#19

Updated by Marina Bey about 3 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!

#20

Updated by Benni Mack almost 3 years ago

  • Status changed from Resolved to Closed
#21

Updated by Oliver Hader almost 2 years ago

  • Related to Bug #86945: Performance PageTree in Workspace added

Also available in: Atom PDF