Actions
Bug #61184
closed
Backend - Page Tree in Workspace & Publish content - Performance Issue - BackendUtility::countVersionsOfRecordsOnPage
Status:
Closed
Priority:
Must have
Assignee:
-
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2014-08-26
Due date:
% Done:
0%
Estimated time:
TYPO3 Version:
6.2
PHP Version:
Tags:
Complexity:
Is Regression:
No
Sprint Focus:
Description
When working in a workspace (Typo3 Backend), loading of the page tree is very slow and sometimes resolving in a connection timeout.
Also, when applying / publish changes of a workspace to the live workspace, changes are getting lost caused by the connection timeout.
That's because, there are a lot of SQL queries which took more than 8 seconds to be processed.
The bottle neck is the following method:
\TYPO3\CMS\Backend\Utility\BackendUtility::countVersionsOfRecordsOnPage
In our case, this method generates queries like this:
mysql> EXPLAIN SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 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; +----+-------------+-------+------+------------------+--------+---------+-------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+------------------+--------+---------+-------+-------+-------------+ | 1 | SIMPLE | B | ref | PRIMARY,parent | parent | 4 | const | 120 | Using where | | 1 | SIMPLE | A | ref | t3ver_oid,parent | parent | 4 | const | 12507 | Using where | +----+-------------+-------+------+------------------+--------+---------+-------+-------+-------------+ 2 rows in set (0.00 sec) mysql> SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 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; Empty set (8.21 sec)
The problem here is the "OR" part of the query.
A.t3ver_oid=B.uid AND A.t3ver_state<>4 OR A.t3ver_oid=B.t3ver_move_id AND A.t3ver_state=4
That's only one of round about 2.000 queries to be need to generate the page tree, and at least 15 of them takes more than 8 seconds.
The solution is, to split this kind of queries into two and merge the results together.
mysql> EXPLAIN SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 AND A.t3ver_oid=B.uid AND A.t3ver_state<>4 AND A.deleted=0 AND B.deleted=0; +----+-------------+-------+------+------------------+-----------+---------+------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+------------------+-----------+---------+------------------------------+------+-------------+ | 1 | SIMPLE | B | ref | PRIMARY,parent | parent | 4 | const | 120 | Using where | | 1 | SIMPLE | A | ref | t3ver_oid,parent | t3ver_oid | 8 | typo3_cms_switch.B.uid,const | 4 | Using where | +----+-------------+-------+------+------------------+-----------+---------+------------------------------+------+-------------+ 2 rows in set (0.00 sec) mysql> EXPLAIN SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 AND A.t3ver_oid=B.t3ver_move_id AND A.t3ver_state=4 AND A.deleted=0 AND B.deleted=0; +----+-------------+-------+------+------------------+-----------+---------+----------------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+------------------+-----------+---------+----------------------------------------+------+-------------+ | 1 | SIMPLE | B | ref | parent | parent | 4 | const | 120 | Using where | | 1 | SIMPLE | A | ref | t3ver_oid,parent | t3ver_oid | 8 | typo3_cms_switch.B.t3ver_move_id,const | 4 | Using where | +----+-------------+-------+------+------------------+-----------+---------+----------------------------------------+------+-------------+ 2 rows in set (0.00 sec) mysql> SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 AND A.t3ver_oid=B.uid AND A.t3ver_state<>4 AND A.deleted=0 AND B.deleted=0; Empty set (0.00 sec) mysql> SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 AND A.t3ver_oid=B.t3ver_move_id AND A.t3ver_state=4 AND A.deleted=0 AND B.deleted=0; Empty set (0.70 sec)
So this construct is 7.3 seconds faster for only one query.
Please take a look on the attached patch, which solves the issue on my end.
Files
Updated by 
Updated by 
Updated by 
Updated by 
Updated by
Actions