TYPO3 Core

I have a TYPO3 v10 platform with 9 workspaces which contain about ca. 3000 pages for each workspace.

There is a performance problem if I migrate TYPO3 v10 databases from MySQL 5.6 to MySQL 8.0.

Reloading page tree on Workspace it takes ca. 3500 ms with MySql 8.0 (the same operation in mysql5 takes us ca. 600 ms).

Also in general TYPO3 v10 on mysql 8.0 is slower than on MySql 5.6.

We analyzed the problem with DBA experts and powerful monitoring software.

We have found that in some cases using the "groub by" clause is a performance killer, you should use the "ditinct" clause instead.

Here is an example:

Original query:

select `B`.`pid` as `pageId` from `tt_content` `B` 
 where ((`B`.`uid` in (select `A`.`t3ver_oid` from `tt_content` `A`
where (`A`.`t3ver_oid` > 0) and (`A`.`t3ver_wsid` = 2) and (`A`.`t3ver_state` <> 4) and (`A`.`deleted` = '*****'))) 
 or (`B`.`t3ver_move_id` in 
 (select `A`.`t3ver_oid` from `tt_content` `A` where (`A`.`t3ver_oid` > 0) and (`A`.`t3ver_wsid` = 2) and (`A`.`t3ver_state` = 4) and (`A`.`deleted` = '*****')))) 
 and (`B`.`deleted` = '*****') group by `B`.`pid`;

elapsed 0.172 secondi

Query with distinct without group by:

select distinct `B`.`pid` as `pageId` from `tt_content` `B` 
 where ((`B`.`uid` in (select `A`.`t3ver_oid` from `tt_content` `A`
where (`A`.`t3ver_oid` > 0) and (`A`.`t3ver_wsid` = 2) and (`A`.`t3ver_state` <> 4) and (`A`.`deleted` = '*****'))) 
 or (`B`.`t3ver_move_id` in 
 (select `A`.`t3ver_oid` from `tt_content` `A` where (`A`.`t3ver_oid` > 0) and (`A`.`t3ver_wsid` = 2) and (`A`.`t3ver_state` = 4) and (`A`.`deleted` = '*****')))) 
 and (`B`.`deleted` = '*****')

elapsed di 0.079 secondi