Bug #86945

Epic #88474: Page tree performance in 9.5

Performance PageTree in Workspace

Added by Alexander Opitz over 1 year ago. Updated 5 months ago.

Status:
Closed
Priority:
Should have
Category:
Workspaces
Target version:
-
Start date:
2018-11-16
Due date:
% Done:

100%

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

Related to TYPO3 Core - Bug #89464: PageTree in workspace not loading Closed 2019-10-21
Related to TYPO3 Core - Bug #80898: No page tree in a workspaces Closed 2017-04-19

Associated revisions

Revision 98c74eb2 (diff)
Added by Alexander Opitz 9 months ago

[TASK] Speed up DB query for tables with versions

Eliminating OR combination of subselects within same table, as they get
wrongly managed by DBMS (mostly MySQL). Using JOIN and UNION reduces
time to execute (on larger data sets) from 10 s to 0.01 s. This helps to
get the page tree view working if an editor is inside workspace.

Resolves: #86945
Releases: master, 9.5, 8.7
Change-Id: I7a5aa66baa6cef160de66fff3f80aec49b46295f
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/59037
Tested-by: TYPO3com <>
Tested-by: Susanne Moog <>
Tested-by: Benni Mack <>
Reviewed-by: Susanne Moog <>
Reviewed-by: Benni Mack <>

Revision 0dab9b41 (diff)
Added by Alexander Opitz 9 months ago

[TASK] Speed up DB query for tables with versions

Eliminating OR combination of subselects within same table, as they get
wrongly managed by DBMS (mostly MySQL). Using JOIN and UNION reduces
time to execute (on larger data sets) from 10 s to 0.01 s. This helps to
get the page tree view working if an editor is inside workspace.

Resolves: #86945
Releases: master, 9.5, 8.7
Change-Id: I7a5aa66baa6cef160de66fff3f80aec49b46295f
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/61604
Tested-by: TYPO3com <>
Tested-by: Benni Mack <>
Reviewed-by: Benni Mack <>

Revision 28c285e7 (diff)
Added by Alexander Opitz 9 months ago

[TASK] Speed up DB query for tables with versions

Eliminating OR combination of subselects within same table, as they get
wrongly managed by DBMS (mostly MySQL). Using JOIN and UNION reduces
time to execute (on larger data sets) from 10 s to 0.01 s. This helps to
get the page tree view working if an editor is inside workspace.

Resolves: #86945
Releases: master, 9.5, 8.7
Change-Id: I7a5aa66baa6cef160de66fff3f80aec49b46295f
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/61605
Tested-by: TYPO3com <>
Tested-by: Benni Mack <>
Reviewed-by: Benni Mack <>

History

#1 Updated by Alexander Opitz over 1 year ago

  • Description updated (diff)

#2 Updated by Alexander Opitz over 1 year 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

#3 Updated by Alexander Opitz over 1 year ago

  • Description updated (diff)

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

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

#6 Updated by Gerrit Code Review over 1 year 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

#7 Updated by Gerrit Code Review over 1 year 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

#8 Updated by Tymoteusz Motylewski almost 1 year ago

  • Parent task set to #88474

#9 Updated by Gerrit Code Review 9 months 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

#10 Updated by Alexander Opitz 9 months ago

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

#11 Updated by Gerrit Code Review 9 months 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

#12 Updated by Alexander Opitz 9 months ago

  • Status changed from Under Review to Resolved

#13 Updated by Oliver Hader 7 months ago

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

#14 Updated by Oliver Hader 7 months 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

#15 Updated by Oliver Hader 7 months ago

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

#16 Updated by Oliver Hader 7 months ago

→ reverted in issue #89464

#18 Updated by Benni Mack 5 months ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF