TYPO3 Core

I have the same Problem after Upgrading TYPO3 to 8.7.28 or later.

In Bug #86945 the PageTree Performance for Workspace are optimized.

Since this change, the page tree will no longer work in certain cases.

I use Powermail and i have a lot of entries in the Table `tx_powermail_domain_model_answer`. Over 1k of the 100k database entries are versioned.

In `TYPO3\CMS\Workspaces\Service\WorkspaceService::fetchPagesWithVersionsInTable($workspaceId, $tableName)` the folowing statement is gernerated:

SELECT `B`.`pid` AS `pageId` FROM `tx_powermail_domain_model_answer` `B` 
INNER JOIN `tx_powermail_domain_model_answer` `A` ON `B`.`uid` = `A`.`t3ver_oid` 
WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 2) AND (`A`.`t3ver_state` <> 4) AND ((`B`.`deleted` = 0) AND (`A`.`deleted` = 0)) 
GROUP BY `B`.`pid` 
UNION SELECT `B`.`pid` AS `pageId` FROM `tx_powermail_domain_model_answer` `B` 
INNER JOIN `tx_powermail_domain_model_answer` `A` ON `B`.`t3ver_move_id` = `A`.`t3ver_oid` 
WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 2) AND (`A`.`t3ver_state` = 4) AND ((`B`.`deleted` = 0) AND (`A`.`deleted` = 0)) 
GROUP BY `B`.`pid`

This query runs into a timeout and the page tree keeps empty. so workspaces does not work anymore.

Explain select:

+------+---------------+-------------+--------------+---------------------------+-----------------+----------+------------------------+--------+-------------------------------------------------------------------------------+
| id   | select_type   |   table     |    type      |      possible_keys        |      key        | key_len  |          ref           | rows   |                                     Extra                                     |
+------+---------------+-------------+--------------+---------------------------+-----------------+----------+------------------------+--------+-------------------------------------------------------------------------------+
| 1    | PRIMARY       | A           | index_merge  | parent,t3ver_oid,deleted  | parent,deleted  | 4,2      | NULL                   | 719    | Using intersect(parent,deleted); Using where; Using temporary; Using filesort |
| 1    | PRIMARY       | B           | eq_ref       | PRIMARY,parent,deleted    | PRIMARY         | 4        | A.t3ver_oid            | 1      | Using where                                                                   |
| 2    | UNION         | B           | ref          | parent,deleted            | deleted         | 2        | const                  | 69425  | Using where; Using temporary; Using filesort                                  |
| 2    | UNION         | A           | ref          | parent,t3ver_oid,deleted  | t3ver_oid       | 8        | B.t3ver_move_id,const  | 48     | Using where                                                                   |
| NULL | UNION RESULT  | <union1,2>  | ALL          | NULL                      | NULL            | NULL     | NULL                   | NULL   | Using temporary                                                               |
+------+---------------+-------------+--------------+---------------------------+-----------------+----------+------------------------+--------+-------------------------------------------------------------------------------+

I have testet the query directly in the mysql terminal on a mysql 5.6.33 server. Ther query returns an empty set in 1 min 57 sec.

After Downgrading TYPO3 to version 8.7.27 all works like a charm

Patch set 2 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/+/62206

Thanks rengaw83 for the details... I did not spot them before creating my previous patch.
In case it is causing a timeout, the only fix I see currently is to revert the previous change.

Alright...

• could reproduce on MySQL v5.6.48 (Docker)
• could not reproduce on MySQL 5.7.25 (Docker), 5.7.27 (source)

Server version: 5.6.46 MySQL Community Server (GPL)

mysql> SELECT `B`.`pid` AS `pageId` FROM `tx_powermail_domain_model_answer` `B`
    -> INNER JOIN `tx_powermail_domain_model_answer` `A` ON `B`.`uid` = `A`.`t3ver_oid`
    -> WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 2) AND (`A`.`t3ver_state` <> 4) AND ((`B`.`deleted` = 0) AND (`A`.`deleted` = 0))
    -> GROUP BY `B`.`pid`
    -> UNION SELECT `B`.`pid` AS `pageId` FROM `tx_powermail_domain_model_answer` `B`
    -> INNER JOIN `tx_powermail_domain_model_answer` `A` ON `B`.`t3ver_move_id` = `A`.`t3ver_oid`
    -> WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 2) AND (`A`.`t3ver_state` = 4) AND ((`B`.`deleted` = 0) AND (`A`.`deleted` = 0))
    -> GROUP BY `B`.`pid`;
Empty set (1 min 6.29 sec)

Server version: 5.7.25 MySQL Community Server (GPL)

mysql> SELECT `B`.`pid` AS `pageId` FROM `tx_powermail_domain_model_answer` `B`
    -> INNER JOIN `tx_powermail_domain_model_answer` `A` ON `B`.`uid` = `A`.`t3ver_oid`
    -> WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 2) AND (`A`.`t3ver_state` <> 4) AND ((`B`.`deleted` = 0) AND (`A`.`deleted` = 0))
    -> GROUP BY `B`.`pid`
    -> UNION SELECT `B`.`pid` AS `pageId` FROM `tx_powermail_domain_model_answer` `B`
    -> INNER JOIN `tx_powermail_domain_model_answer` `A` ON `B`.`t3ver_move_id` = `A`.`t3ver_oid`
    -> WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 2) AND (`A`.`t3ver_state` = 4) AND ((`B`.`deleted` = 0) AND (`A`.`deleted` = 0))
    -> GROUP BY `B`.`pid`;
Empty set (0.04 sec)

Thus, on MySQL 5.6 this will at least run into an XHR timeout leaving the TYPO3 page tree empty...

... and the previous queries have been like this (reverted the mentioned change) ...

mysql> SELECT `B`.`pid` AS `pageId` FROM `tx_powermail_domain_model_answer` `B` WHERE (
    -> (`B`.`uid` IN (
    ->     SELECT `A`.`t3ver_oid` FROM `tx_powermail_domain_model_answer` `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 `tx_powermail_domain_model_answer` `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`;
Empty set (0.10 sec)

In this use-case 0.04 vs. 0.10 on MySQL 5.7

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/+/62208

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/c/Packages/TYPO3.CMS/+/62209

Patch set 2 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/+/62207

Patch set 2 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/+/62208

The main issue with the patch (that is reverted) is the changed query generation in the QueryBuilder since the patch was originally worked out. It now generates an 'INNER JOIN' with back references (which is a mess with older MySQL versions) instead of a JOIN with subselect as it was generated with QueryBuilder one year ago. Thats why, this optimization leads to a big missoptimization for MySQL <=5.6. So to get better performance we now need to do more hand optimization instead of using QueryBuilder (or find a way around).

With MySQL 5.7 and newer or MariaDB 10.0 and newer the original issue (which was tried to fix) with the "OR" combination of both selects was resolved by a better SQL execution plan.

As newer MySQL/MariaDB Versions are more and more in use and all users are moving along I'll do not fix the original issue with digging again into QueryBuilding. The old state (which was faster as the state after the patches) is restored with this revert. If you have a old MySQL (<=5.6 or MariaDB <= 5.5), a large database, use workspaces and long waits while loading the page tree please upgrade to a newer version of MySQL (5.7 or newer [latest is 8.0.16]) or MariaDB (Partially 10.0 and fully 10.1 or newer [latest is 10.4.10]). No further development will be done here.