Issue Bug Closed

#61184Backend - Page Tree in Workspace & Publish content - Performance Issue - BackendUtility::countVersionsOfRecordsOnPage

Andreas Lingott 08/26/14, 10:25 updated 05/27/15, 13:37

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.

Reviews 4