Bug #86945

Performance PageTree in Workspace

Added by Alexander Opitz 25 days ago. Updated 6 days ago.

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

0%

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.

History

#1 Updated by Alexander Opitz 25 days ago

  • Description updated (diff)

#2 Updated by Alexander Opitz 8 days 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 8 days ago

  • Description updated (diff)

#4 Updated by Gerrit Code Review 7 days 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 6 days 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

Also available in: Atom PDF