TYPO3 Core

We just upgraded from Typo3 7.6 to 8.7.15. The pagetree with workspace in draft mode is not functional.

Whenever a user goes into draft mode, the following SQL query takes forever to execute and when multiple people do this action, the server becomes dead.

SELECT `B`.`pid` AS `pageId` FROM `sys_file_metadata` `B` WHERE (`B`.`uid` IN (SELECT `A`.`t3ver_oid` FROM `sys_file_metadata` `A` WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 1) AND (`A`.`t3ver_state` <> 4))) OR (`B`.`t3ver_move_id` IN (SELECT `A`.`t3ver_oid` FROM `sys_file_metadata` `A` WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 1) AND (`A`.`t3ver_state` = 4))) GROUP BY `B`.`pid`
+----+--------------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows  | Extra                                        |
+----+--------------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+
|  1 | PRIMARY            | B     | ALL  | NULL          | NULL | NULL    | NULL | 14562 | Using where; Using temporary; Using filesort |
|  3 | DEPENDENT SUBQUERY | A     | ALL  | t3ver_oid     | NULL | NULL    | NULL | 14562 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | A     | ALL  | t3ver_oid     | NULL | NULL    | NULL | 14562 | Using where                                  |
+----+--------------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+

Following the https://forge.typo3.org/issues/80898, the group by pid is the faulty part. Also, the pid is used to make inner join, but since there is no index, mysql evaluates all records in the table: sys_file_metadata

By adding a index on pid, the query runs in milliseconds instead of minutes.

mysql> explain SELECT `B`.`pid` AS `pageId` FROM `sys_file_metadata` `B` WHERE (`B`.`uid` IN (SELECT `A`.`t3ver_oid` FROM `sys_file_metadata` `A` WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 1) AND (`A`.`t3ver_state` <> 4))) OR (`B`.`t3ver_move_id` IN (SELECT `A`.`t3ver_oid` FROM `sys_file_metadata` `A` WHERE (`A`.`pid` = -1) AND (`A`.`t3ver_wsid` = 1) AND (`A`.`t3ver_state` = 4))) GROUP BY `B`.`pid`;
+----+--------------------+-------+------+---------------+------+---------+-------+-------+----------------------------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref   | rows  | Extra                                        |
+----+--------------------+-------+------+---------------+------+---------+-------+-------+----------------------------------------------+
|  1 | PRIMARY            | B     | ALL  | NULL          | NULL | NULL    | NULL  | 14628 | Using where; Using temporary; Using filesort |
|  3 | DEPENDENT SUBQUERY | A     | ref  | t3ver_oid,pid | pid  | 4       | const |     1 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | A     | ref  | t3ver_oid,pid | pid  | 4       | const |     1 | Using where                                  |
+----+--------------------+-------+------+---------------+------+---------+-------+-------+----------------------------------------------+