Issue Task Closed

#107529Performance: Bad INDEX usage in PageRepository::getWorkspaceVersionOfRecord

Stefan Froemken 09/24/25, 09:20 updated 11/09/26, 11:13

Description

Hi,

we have a website with a lot of menu entries. In LIVE WS it renders quite fast, but with activated Workspace the rendering needs 12-20 seconds (on remote) and 4-6 seconds (local DDEV). With help of mysqlreport I detected following query:

SELECT [a_lot_of_columns]
FROM `pages`
WHERE (`t3ver_wsid` = 93)
AND (
    (
        (
            (
                (`uid` = 1877)
                AND (`t3ver_state` = 1)
            )
        )
        OR (`t3ver_oid` = 1877)
    )
)
AND (`pages`.`deleted` = 0)
LIMIT 1;

The EXPLAIN:

1     SIMPLE     pages     index_merge     PRIMARY,t3ver_oid     PRIMARY,t3ver_oid     4,8     NULL     2     Using union(PRIMARY,t3ver_oid); Using where

Sure, it’s quite fast with 0.0022 seconds, but this query was called 1946 time.

I have optimized that query a little bit with an INDEX:

CREATE INDEX idx_pages_uid_state ON pages (uid, t3ver_state);

Now, we have 0,0019 seconds. That’s not realy a good catch. I ask KI for a better solution: UNION ALL. Hmm, Doctrine does not have a UNION ALL intergration, but I found a very huge query structure in TYPO3 class handling sys_refindex, where 3 QueryBuilders work together to get UNION ALL done.

With that solution such queries can be reduzed to 0,0008-0,0011 seconds.

Reviews 1

History 56

  1. Benni Mack commented and changed status Under Review Closed <span data-processor="relativetime" data-value="2026-11-09T11:13:26+00:00" > 11/09/26, 11:13 </span>

    Addressed by #108901 (f90b04dff2f, Gerrit 90784, in 14.3): getWorkspaceVersionOfRecord() now receives the full live record and checks access in PHP, which halves the queries per record. The query itself uses an index_merge over PRIMARY and t3ver_oid (see EXPLAIN above), so an additional index or UNION ALL brings only marginal gains. The remaining cost is one lookup per record in large menus, which would need batching and is beyond this issue. Closing as resolved; this improvement is not part of 13.4.