Review 14.3 Merged

95299[BUGFIX] Cover translation lookup by a single index

Stefan Bürk CR +2 V +2 Normal +209 −16 updated 08/13/26, 18:38

Commit message

Translation lookups match a translation either by its translation source
field holding the uid of the record, or - for translations written
without a translation source - by an empty translation source together
with the translation origin pointer field holding that uid.

With an index on the translation source field alone, MySQL reduces the
constraint to a range over that field, which covers every record without
a translation source, and discards the index entirely for the unbounded
lookup. On a table of 200k records the bounded lookup grows from 2ms to
150ms, and the unbounded one, issued once per record while rendering the
list and page modules, to 185ms. MariaDB resolves the constraint through
an index merge and degrades only where no index on the translation
origin pointer field exists.

The generated "translation_source" index therefore spans the translation
source field, the translation origin pointer field and the language
field, so that both alternatives resolve as ranges on one index. The
former single column index remains its leftmost prefix, so lookups
filtering on the translation source alone are unaffected. The column
order is relevant: with the language field in second position the
unbounded lookup still costs 184ms on MySQL.

The language related indexes are further generated from the language
capability of the schema instead of from the question whether the
automatic schema generation added the underlying columns itself. A table
declaring for instance its own "sys_language_uid" column in an
ext_tables.sql file silently ended up without them.

The 13.4 backport differs in implementation only: schema generation is
still TCA array based on that branch and has no language capability, so
the index is derived from the ctrl configuration instead. The resulting
index is the same.

Review

  • Code review CR +2 Done
    • Stefan Bürk +2
  • Verified V +2 Done
    • Stefan Bürk +2
    • core-ci +1 CI

Issues 2

Files 3

Review log 59

  1. Stefan Bürk uploaded patch set 1 08/12/26, 16:28

  2. Stefan Bürk uploaded patch set 2 08/12/26, 16:35

  3. core-ci voted on patch set 1 V -1 08/12/26, 16:49 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/106562

  4. core-ci voted on patch set 2 V -1 08/12/26, 16:56 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/106565

  5. core-ci voted on patch set 2 V +1 08/12/26, 17:10 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/106565

  6. Stefan Bürk uploaded patch set 3 V +1 08/12/26, 19:27

  7. core-ci voted on patch set 3 V +1 08/12/26, 19:37 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/106569

  8. Stefan Bürk voted on patch set 3 CR +2 V +2 08/13/26, 18:38

  9. Stefan Bürk merged patch set 4 08/13/26, 18:38