Project

General

Profile

Actions

Bug #86945

closed

Epic #88474: Page tree performance in 9.5

Performance PageTree in Workspace

Added by Alexander Opitz over 5 years ago. Updated over 4 years ago.

Status:
Closed
Priority:
Should have
Category:
Workspaces
Target version:
-
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.

Related issues 2 (0 open2 closed)

Related to TYPO3 Core - Bug #89464: PageTree in workspace not loadingClosed2019-10-21

Actions
Related to TYPO3 Core - Bug #80898: No page tree in a workspacesClosedXavier Perseguers2017-04-19

Actions
Actions #1

Updated by Alexander Opitz over 5 years ago

  • Description updated (diff)
Actions #2

Updated by Alexander Opitz over 5 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
Actions #3

Updated by Alexander Opitz over 5 years ago

  • Description updated (diff)
Actions #4

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

Actions #5

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

Actions #6

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

Actions #7

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

Actions #8

Updated by Tymoteusz Motylewski almost 5 years ago

  • Parent task set to #88474
Actions #9

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

Actions #10

Updated by Alexander Opitz over 4 years ago

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

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

Actions #12

Updated by Alexander Opitz over 4 years ago

  • Status changed from Under Review to Resolved
Actions #13

Updated by Oliver Hader over 4 years ago

  • Related to Bug #89464: PageTree in workspace not loading added
Actions #14

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

Actions #15

Updated by Oliver Hader over 4 years ago

  • Related to Bug #80898: No page tree in a workspaces added
Actions #16

Updated by Oliver Hader over 4 years ago

→ reverted in issue #89464

Actions #18

Updated by Benni Mack over 4 years ago

  • Status changed from Resolved to Closed
Actions

Also available in: Atom PDF