#107529Performance: Bad INDEX usage in PageRepository::getWorkspaceVersionOfRecord
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
Related issues 1
History 56
-
Gerrit Code Review pushed patch set 1 for main to change 90783 status
NewUnder Review <span data-processor="relativetime" data-value="2025-09-24T09:28:40+00:00" > 09/24/25, 09:28 </span> -
Oliver Hader changed target version <span data-processor="relativetime" data-value="2026-01-23T12:29:50+00:00" > 01/23/26, 12:29 </span>
-
Benni Mack changed target version <span data-processor="relativetime" data-value="2026-03-31T09:05:26+00:00" > 03/31/26, 09:05 </span>
-
Benni Mack changed target version <span data-processor="relativetime" data-value="2026-04-21T11:25:13+00:00" > 04/21/26, 11:25 </span>
-
Benni Mack commented and changed status
Under ReviewClosed <span data-processor="relativetime" data-value="2026-11-09T11:13:26+00:00" > 11/09/26, 11:13 </span> -
Benni Mack changed Related to #108901 <span data-processor="relativetime" data-value="2026-11-09T11:13:31+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.