#110454Translation lookup can not be resolved through an index
Description
Problem
Since #110281 the translation lookup matches a translation in one of two ways:
either its translation source field holds the uid of the record, or – for
translations written without a translation source – its translation source field
is empty and its translation origin pointer field holds that uid. The language
field and the deleted flag are matched in addition.
The generated index “translation_source” spans the translation source field
alone, “language_identifier” spans the translation origin pointer field and the
language field. Neither of them covers both alternatives of the constraint, so it
can not be resolved through a single index.
The affected lookups are TYPO3\CMS\Backend\Domain\Repository\Localization\LocalizationRepository::getRecordTranslation(),
its unbounded counterpart getRecordTranslations(), and, on 14.3 and 13.4,
TYPO3\CMS\Backend\Utility\BackendUtility::getRecordLocalization(). The unbounded
variant is issued once per record while rendering the list and page modules, the
bounded one once per record during localize and copy operations in DataHandler.
Measurements
Table of 201717 records, 100858 of them with an empty translation source. Time
per query, warm buffer pool:
| Engine and schema | Lookup | Before | After |
|---|---|---|---|
| MySQL 8.0 and 8.4 | single translation | 2.4 ms | 150 ms |
| MySQL 8.0 and 8.4 | all translations | 2.1 ms | 183 ms |
| MariaDB 10.11 and 11.8 | single translation | 2.7 ms | 3.0 ms |
| MariaDB 10.11 and 11.8, no index on the translation origin pointer field | single translation | 3.7 ms | 132 ms |
| MariaDB 10.11 and 11.8, no index on the translation origin pointer field | all translations | 2.9 ms | 132 ms |
MySQL reduces the condition to a range over the translation source alone, which
covers every record with an empty translation source – 98254 index entries at a
key length of 4, each followed by a primary key lookup – and discards the index
entirely for the unbounded variant, which then reports type ALL over 196505
rows. MariaDB resolves the condition through an index merge and stays unaffected
as long as an index on the translation origin pointer field is present. That
index is generated only since 14.0, and only when the automatic schema
generation added the columns itself.
Rewriting the condition into a union of its two alternatives repairs MySQL on a
complete schema, but leaves the case without an index on the translation origin
pointer field at 121 to 149 ms. It is therefore not a substitute for the index.
Solution
Extend the generated index so that both alternatives resolve as ranges on one
index:
bc. KEY translation_source (l10n_source, l18n_parent, sys_language_uid)
The key length grows from 4 to 12, the row estimate drops from 98254 to 3, and
both lookups return to 2.3 to 3.9 ms on MySQL and MariaDB alike. The previous
single column index remains the leftmost prefix of the new one, so lookups
filtering on the translation source alone are unaffected. The column order is
relevant: keeping the language field in second position still costs 42 ms for
the bounded and 184 ms for the unbounded lookup on MySQL. A trailing deleted
column gains nothing.
Independent of that, both language related indexes are currently generated only
when the automatic schema generation added the underlying columns itself. A
table declaring for example its own sys_language_uid column in ext_tables.sql
silently receives neither index. Index generation is therefore bound to the
language capability of the schema instead.
Affected versions
main, 14.3 and 13.4. On 13.4 the schema generation is still TCA array based and
does not generate the language_identifier index at all, so the change differs
there while the resulting index is the same.