TYPO3 Core

I just don't know how to use "DISTINCT" instead of "GROUP BY" with Doctrine DBAL.

Thus, using DISTINCT removes the filesort on the temporary joined table.

Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/52506

This problem also applies to TYPO3 7.6.18 LTS where this query is used:

SELECT B.uid as live_uid, B.pid as live_pid, A.uid as offline_uid FROM sys_file_reference A,sys_file_reference B WHERE A.pid=-1 AND A.t3ver_wsid=1 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 GROUP BY live_pid;

I also see a problem with the tt_content query. This also seem to increase in duration in relation to the table size:

SELECT B.uid as live_uid, B.pid as live_pid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND A.t3ver_wsid=1 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 GROUP BY live_pid;

Do we need a seperate Issue for 7.6 LTS ?

Thanks Jens. I expected it to be true for 7.6 as well but did not test yet. Please go to https://review.typo3.org/52506 and write a comment for the commit message so that branch "7.6" is targeted as well.

Xavier Perseguers wrote:

Thanks Jens. I expected it to be true for 7.6 as well but did not test yet. Please go to https://review.typo3.org/52506 and write a comment for the commit message so that branch "7.6" is targeted as well.

I don't think its that easy because the code for 7.6 is totally different or am I totally wrong about it?

• The target of the patch is not whether the "very same" patch should be applied to another branch but whether the same problem (and fix) - generally speaking - is valjd for another branch
• ORDER BY NULL seems to be very MySQL specific

The usage of DISTINCT (or any other statement) has to be checked for other DBMS as well (thus, not only for MySQL).
Besides that, the code seems to be wrong in general which was basically caused by the Doctrine DBAL migration in https://review.typo3.org/#/c/48600/ - $pageIds[$row['uid']] = $row; does not make sense, since uid is not part of the selected columns.

Patch set 3 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/52506

How is the Status for 7.6.18? I can reproduce it, and the ORDER BY NULL increases the performance. Maybe there is a better Solution. But better get this dirty one than nothing. How to get it in the 7.6 branch?

Patch set 1 for branch TYPO3_8-7 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/52853

No fix for 7.6 ? :(

Hi, I'd like to have this bug reopenend. We recently updated a TYPO3 Instance to 8.7.13 with around 40 Workspaces. The patch splits the query into subqueries, but in the end also does a GROUP BY. This led to queries running for 300 seconds and longer. I hotfixed it by using the DISTINCT Variant of Patchset 1, this works without problems.

Hi, we have the same problem in TYPO3 8. Question to Sascha or anyone else: would you please post your solution with the DISTINCT-part? Thanks a lot!