Bug #61184
Updated by Alexander Opitz over 9 years ago
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: <pre> 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; +----+-------------+-------+------+------------------+--------+---------+-------+-------+-------------+ 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) </pre> The problem here is the "OR" part of the query. <pre> A.t3ver_oid=B.uid AND A.t3ver_state<>4 OR A.t3ver_oid=B.t3ver_move_id AND A.t3ver_state=4 </pre> 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. <pre> 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) </pre> 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.