Actions
Bug #98350
closed
MySql 8 performance reloading page tree on Workspace
Start date:
2022-09-15
Due date:
% Done:
0%
Estimated time:
TYPO3 Version:
10
PHP Version:
7.4
Tags:
mysql mysql8 8 performance database workpsaces
Complexity:
Is Regression:
Sprint Focus:
Description
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
Updated by 
Updated by 
Updated by
Actions