#110979Bad query performance on sys_file_reference language overlay
Description
TYPO3 12
Mysql 8.4
On a production system with 80+ sites, we experience bad performance on that kind of db query:
SELECT * FROM `sys_file_reference`
WHERE (`pid` = 604) AND (`sys_language_uid` = 1)
AND (`l10n_parent` = 4079168)
AND (((`sys_file_reference`.`deleted` = 0)
AND (((`sys_file_reference`.`t3ver_wsid` = 0)
AND (((`sys_file_reference`.`t3ver_oid` = 0) OR (`sys_file_reference`.`t3ver_state` = 4)))))
AND (`sys_file_reference`.`hidden` = 0))) LIMIT 1;
That query lasts for 30s and is issued regularly from frontend.
This query is issued by \TYPO3\CMS\Core\Domain\Repository\PageRepository::getRecordOverlay
The wrong index is selected by the optimizer:
.clipboard-202610091458-refjm.png
The only solution we found is to add an optimizer hint to use the index combined_1:
$olrow = $queryBuilder
// Hint the optimizer to use the combined_1
//->select('*')
->selectLiteral('/*+ INDEX(sys_file_reference combined_1) */ *')
->from($table)
.clipboard-202610091500-l5xix.png
An other solution would be to remove the deleted index, which is not very relevant on frontend, given the cardinality of deleted=0.
But it may be used by the recycler.
Reviews 0
No change resolves or names this issue in its commit message, and no comment links one.
Related issues 2
Files 2
History 8
-
Rémy DANIEL changed Related to #93547 <span data-processor="relativetime" data-value="2026-10-09T13:02:42+00:00" > 10/09/26, 13:02 </span>
-
Rémy DANIEL changed Related to #54725 <span data-processor="relativetime" data-value="2026-10-09T15:20:54+00:00" > 10/09/26, 15:20 </span>
-
Wolfgang Klinger commented <span data-processor="relativetime" data-value="2026-10-09T15:29:02+00:00" > 10/09/26, 15:29 </span>
-
Garvin Hicking commented and changed status
NewNeeds Feedback <span data-processor="relativetime" data-value="2026-10-09T17:06:57+00:00" > 10/09/26, 17:06 </span>Can you also test this against v13 (or better yet: main)? v12 is no longer supported by the community.
-
Rémy DANIEL commented <span data-processor="relativetime" data-value="2026-10-09T18:43:17+00:00" > 10/09/26, 18:43 </span>
> 1. Which exact MySQL version is it, and is it plain MySQL or a managed service?
It’s a plain MySQL 8.4.7
> 2. Please paste the output of SHOW CREATE TABLE sys_file_reference. Where does the language_identifier index come from?
The language_identifier come from a backport of https://git.typo3.org/typo3/typo3/-/commit/1452d95312d8f0718570dac88b07640085949f52
CREATE TABLE `sys_file_reference` ( `uid` int unsigned NOT NULL AUTO_INCREMENT, `pid` int unsigned NOT NULL DEFAULT '0', `tstamp` int unsigned NOT NULL DEFAULT '0', `crdate` int unsigned NOT NULL DEFAULT '0', `deleted` smallint unsigned NOT NULL DEFAULT '0', `hidden` smallint unsigned NOT NULL DEFAULT '0', `t3ver_oid` int unsigned NOT NULL DEFAULT '0', `t3ver_wsid` int unsigned NOT NULL DEFAULT '0', `t3ver_state` smallint NOT NULL DEFAULT '0', `t3ver_stage` int NOT NULL DEFAULT '0', `sys_language_uid` int NOT NULL DEFAULT '0', `l10n_parent` int unsigned NOT NULL DEFAULT '0', `l10n_diffsource` mediumblob, `uid_local` int NOT NULL DEFAULT '0', `uid_foreign` int NOT NULL DEFAULT '0', `tablenames` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `fieldname` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `sorting_foreign` int NOT NULL DEFAULT '0', `title` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `alternative` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `link` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `crop` varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `autoplay` smallint NOT NULL DEFAULT '0', `l10n_state` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`uid`), KEY `tablenames_fieldname` (`tablenames`(32),`fieldname`(12)), KEY `deleted` (`deleted`), KEY `uid_local` (`uid_local`), KEY `uid_foreign` (`uid_foreign`), KEY `combined_1` (`l10n_parent`,`t3ver_oid`,`t3ver_wsid`,`t3ver_state`,`deleted`), KEY `t3ver_oid` (`t3ver_oid`,`t3ver_wsid`), KEY `parent` (`pid`,`deleted`,`hidden`), KEY `language_identifier` (`l10n_parent`,`sys_language_uid`) ) ENGINE=InnoDB AUTO_INCREMENT=8136611 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
> 3. How many rows does the table have, how many have sys_language_uid > 0, and how many have pid = 604?
SELECT COUNT(uid) FROM `sys_file_reference`; 6093614 SELECT COUNT(uid) FROM `sys_file_reference` WHERE sys_language_uid > 0; 5279870 SELECT COUNT(uid) FROM `sys_file_reference` WHERE pid = 604; 1108726
We obsiously have an issue in pid=604!
> 4. Please paste SELECT * FROM mysql.innodb_index_stats WHERE table_name = ‘sys_file_reference’.
[(none)] mysql> SELECT * FROM mysql.innodb_index_stats WHERE table_name = 'sys_file_reference'; +---------------+--------------------+----------------------+---------------------+--------------+------------+-------------+----------------------------------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+--------------------+----------------------+---------------------+--------------+------------+-------------+----------------------------------------------------------+ | uccife | sys_file_reference | PRIMARY | 2026-10-09 14:29:59 | n_diff_pfx01 | 5902169 | 2000 | uid | | uccife | sys_file_reference | PRIMARY | 2026-10-09 14:29:59 | n_leaf_pages | 194801 | NULL | Number of leaf pages in the index | | uccife | sys_file_reference | PRIMARY | 2026-10-09 14:29:59 | size | 222911 | NULL | Number of pages in the index | | uccife | sys_file_reference | combined_1 | 2026-10-09 14:29:59 | n_diff_pfx01 | 89543 | 9567 | l10n_parent | | uccife | sys_file_reference | combined_1 | 2026-10-09 14:29:59 | n_diff_pfx02 | 89543 | 9567 | l10n_parent,t3ver_oid | | uccife | sys_file_reference | combined_1 | 2026-10-09 14:29:59 | n_diff_pfx03 | 89543 | 9567 | l10n_parent,t3ver_oid,t3ver_wsid | | uccife | sys_file_reference | combined_1 | 2026-10-09 14:29:59 | n_diff_pfx04 | 89543 | 9567 | l10n_parent,t3ver_oid,t3ver_wsid,t3ver_state | | uccife | sys_file_reference | combined_1 | 2026-10-09 14:29:59 | n_diff_pfx05 | 89624 | 9567 | l10n_parent,t3ver_oid,t3ver_wsid,t3ver_state,deleted | | uccife | sys_file_reference | combined_1 | 2026-10-09 14:29:59 | n_diff_pfx06 | 6093543 | 9567 | l10n_parent,t3ver_oid,t3ver_wsid,t3ver_state,deleted,uid | | uccife | sys_file_reference | combined_1 | 2026-10-09 14:29:59 | n_leaf_pages | 9569 | NULL | Number of leaf pages in the index | | uccife | sys_file_reference | combined_1 | 2026-10-09 14:29:59 | size | 10995 | NULL | Number of pages in the index | | uccife | sys_file_reference | deleted | 2026-10-09 14:29:59 | n_diff_pfx01 | 1 | 3 | deleted | | uccife | sys_file_reference | deleted | 2026-10-09 14:29:59 | n_diff_pfx02 | 6089847 | 2000 | deleted,uid | | uccife | sys_file_reference | deleted | 2026-10-09 14:29:59 | n_leaf_pages | 4314 | NULL | Number of leaf pages in the index | | uccife | sys_file_reference | deleted | 2026-10-09 14:29:59 | size | 4966 | NULL | Number of pages in the index | | uccife | sys_file_reference | language_identifier | 2026-10-09 14:29:59 | n_diff_pfx01 | 89449 | 154 | l10n_parent | | uccife | sys_file_reference | language_identifier | 2026-10-09 14:29:59 | n_diff_pfx02 | 113931 | 173 | l10n_parent,sys_language_uid | | uccife | sys_file_reference | language_identifier | 2026-10-09 14:29:59 | n_diff_pfx03 | 6087609 | 2000 | l10n_parent,sys_language_uid,uid | | uccife | sys_file_reference | language_identifier | 2026-10-09 14:29:59 | n_leaf_pages | 6569 | NULL | Number of leaf pages in the index | | uccife | sys_file_reference | language_identifier | 2026-10-09 14:29:59 | size | 7530 | NULL | Number of pages in the index | | uccife | sys_file_reference | parent | 2026-10-09 14:29:59 | n_diff_pfx01 | 16840 | 6568 | pid | | uccife | sys_file_reference | parent | 2026-10-09 14:29:59 | n_diff_pfx02 | 17226 | 6568 | pid,deleted | | uccife | sys_file_reference | parent | 2026-10-09 14:29:59 | n_diff_pfx03 | 19669 | 6568 | pid,deleted,hidden | | uccife | sys_file_reference | parent | 2026-10-09 14:29:59 | n_diff_pfx04 | 6093543 | 6568 | pid,deleted,hidden,uid | | uccife | sys_file_reference | parent | 2026-10-09 14:29:59 | n_leaf_pages | 6568 | NULL | Number of leaf pages in the index | | uccife | sys_file_reference | parent | 2026-10-09 14:29:59 | size | 7531 | NULL | Number of pages in the index | | uccife | sys_file_reference | t3ver_oid | 2026-10-09 14:29:59 | n_diff_pfx01 | 0 | 2 | t3ver_oid | | uccife | sys_file_reference | t3ver_oid | 2026-10-09 14:29:59 | n_diff_pfx02 | 0 | 2 | t3ver_oid,t3ver_wsid | | uccife | sys_file_reference | t3ver_oid | 2026-10-09 14:29:59 | n_diff_pfx03 | 6087609 | 2000 | t3ver_oid,t3ver_wsid,uid | | uccife | sys_file_reference | t3ver_oid | 2026-10-09 14:29:59 | n_leaf_pages | 6567 | NULL | Number of leaf pages in the index | | uccife | sys_file_reference | t3ver_oid | 2026-10-09 14:29:59 | size | 7530 | NULL | Number of pages in the index | | uccife | sys_file_reference | tablenames_fieldname | 2026-10-09 14:29:59 | n_diff_pfx01 | 20 | 18 | tablenames | | uccife | sys_file_reference | tablenames_fieldname | 2026-10-09 14:29:59 | n_diff_pfx02 | 49 | 38 | tablenames,fieldname | | uccife | sys_file_reference | tablenames_fieldname | 2026-10-09 14:29:59 | n_diff_pfx03 | 6073929 | 2000 | tablenames,fieldname,uid | | uccife | sys_file_reference | tablenames_fieldname | 2026-10-09 14:29:59 | n_leaf_pages | 16822 | NULL | Number of leaf pages in the index | | uccife | sys_file_reference | tablenames_fieldname | 2026-10-09 14:29:59 | size | 19327 | NULL | Number of pages in the index | | uccife | sys_file_reference | uid_foreign | 2026-10-09 14:29:59 | n_diff_pfx01 | 297330 | 875 | uid_foreign | | uccife | sys_file_reference | uid_foreign | 2026-10-09 14:29:59 | n_diff_pfx02 | 6090534 | 2000 | uid_foreign,uid | | uccife | sys_file_reference | uid_foreign | 2026-10-09 14:29:59 | n_leaf_pages | 5067 | NULL | Number of leaf pages in the index | | uccife | sys_file_reference | uid_foreign | 2026-10-09 14:29:59 | size | 5800 | NULL | Number of pages in the index | | uccife | sys_file_reference | uid_local | 2026-10-09 14:29:59 | n_diff_pfx01 | 563051 | 873 | uid_local | | uccife | sys_file_reference | uid_local | 2026-10-09 14:29:59 | n_diff_pfx02 | 6089332 | 2000 | uid_local,uid | | uccife | sys_file_reference | uid_local | 2026-10-09 14:29:59 | n_leaf_pages | 5068 | NULL | Number of leaf pages in the index | | uccife | sys_file_reference | uid_local | 2026-10-09 14:29:59 | size | 5799 | NULL | Number of pages in the index | +---------------+--------------------+----------------------+---------------------+--------------+------------+-------------+----------------------------------------------------------+ 44 rows in set (0.01 sec)
> 5. Please paste the values of innodb_stats_persistent, innodb_stats_auto_recalc and innodb_stats_persistent_sample_pages.
innodb_stats_persistent ON innodb_stats_auto_recalc ON innodb_stats_persistent_sample_pages 2000
> 6. Run ANALYZE TABLE sys_file_reference, then run EXPLAIN on the slow query again. Does the key column still say deleted?
I did an optimize table earlier this day, but :
mysql> ANALYZE TABLE sys_file_reference; +---------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------------+---------+----------+----------+ | uccife.sys_file_reference | analyze | status | OK | +---------------------------+---------+----------+----------+ 1 row in set (28.92 sec)
> 7. Run EXPLAIN on the same query without LIMIT 1. Does the key column still say deleted?
Yes, deleted is still the used index.
mysql> explain SELECT * FROM `sys_file_reference` -> WHERE (`pid` = 604) AND (`sys_language_uid` = 1) -> AND (`l10n_parent` = 4079168) -> AND (((`sys_file_reference`.`deleted` = 0) -> AND (((`sys_file_reference`.`t3ver_wsid` = 0) -> AND (((`sys_file_reference`.`t3ver_oid` = 0) OR (`sys_file_reference`.`t3ver_state` = 4))))) -> AND (`sys_file_reference`.`hidden` = 0))) LIMIT 1; +----+-------------+--------------------+------------+-------+---------------------------------------------------------+---------+---------+------+---------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------------+------------+-------+---------------------------------------------------------+---------+---------+------+---------+----------+------------------------------------+ | 1 | SIMPLE | sys_file_reference | NULL | range | deleted,combined_1,t3ver_oid,parent,language_identifier | deleted | 2 | NULL | 2950530 | 0.00 | Using index condition; Using where | +----+-------------+--------------------+------------+-------+---------------------------------------------------------+---------+---------+------+---------+----------+------------------------------------+ 1 row in set, 1 warning (0.01 sec)> 8. Please run the query with EXPLAIN ANALYZE and with the optimizer trace enabled, and attach both. The trace shows why each index was rejected:
SET optimizer_trace = 'enabled=on'; SELECT ... ; -- the slow query SELECT * FROM information_schema.optimizer_trace\G mysql> SELECT * FROM information_schema.optimizer_trace\G *************************** 1. row *************************** QUERY: SELECT * FROM `sys_file_reference` WHERE (`pid` = 604) AND (`sys_language_uid` = 1) AND (`l10n_parent` = 4079168) AND (((`sys_file_reference`.`deleted` = 0) AND (((`sys_file_reference`.`t3ver_wsid` = 0) AND (((`sys_file_reference`.`t3ver_oid` = 0) OR (`sys_file_reference`.`t3ver_state` = 4))))) AND (`sys_file_reference`.`hidden` = 0))) LIMIT 1 TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `sys_file_reference`.`uid` AS `uid`,`sys_file_reference`.`pid` AS `pid`,`sys_file_reference`.`tstamp` AS `tstamp`,`sys_file_reference`.`crdate` AS `crdate`,`sys_file_reference`.`deleted` AS `deleted`,`sys_file_reference`.`hidden` AS `hidden`,`sys_file_reference`.`t3ver_oid` AS `t3ver_oid`,`sys_file_reference`.`t3ver_wsid` AS `t3ver_wsid`,`sys_file_reference`.`t3ver_state` AS `t3ver_state`,`sys_file_reference`.`t3ver_stage` AS `t3ver_stage`,`sys_file_reference`.`sys_language_uid` AS `sys_language_uid`,`sys_file_reference`.`l10n_parent` AS `l10n_parent`,`sys_file_reference`.`l10n_diffsource` AS `l10n_diffsource`,`sys_file_reference`.`uid_local` AS `uid_local`,`sys_file_reference`.`uid_foreign` AS `uid_foreign`,`sys_file_reference`.`tablenames` AS `tablenames`,`sys_file_reference`.`fieldname` AS `fieldname`,`sys_file_reference`.`sorting_foreign` AS `sorting_foreign`,`sys_file_reference`.`title` AS `title`,`sys_file_reference`.`description` AS `description`,`sys_file_reference`.`alternative` AS `alternative`,`sys_file_reference`.`link` AS `link`,`sys_file_reference`.`crop` AS `crop`,`sys_file_reference`.`autoplay` AS `autoplay`,`sys_file_reference`.`l10n_state` AS `l10n_state` from `sys_file_reference` where ((`sys_file_reference`.`pid` = 604) and (`sys_file_reference`.`sys_language_uid` = 1) and (`sys_file_reference`.`l10n_parent` = 4079168) and (`sys_file_reference`.`deleted` = 0) and (`sys_file_reference`.`t3ver_wsid` = 0) and ((`sys_file_reference`.`t3ver_oid` = 0) or (`sys_file_reference`.`t3ver_state` = 4)) and (`sys_file_reference`.`hidden` = 0)) limit 1" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`sys_file_reference`.`pid` = 604) and (`sys_file_reference`.`sys_language_uid` = 1) and (`sys_file_reference`.`l10n_parent` = 4079168) and (`sys_file_reference`.`deleted` = 0) and (`sys_file_reference`.`t3ver_wsid` = 0) and ((`sys_file_reference`.`t3ver_oid` = 0) or (`sys_file_reference`.`t3ver_state` = 4)) and (`sys_file_reference`.`hidden` = 0))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((multiple equal(0, `sys_file_reference`.`t3ver_oid`) or multiple equal(4, `sys_file_reference`.`t3ver_state`)) and multiple equal(604, `sys_file_reference`.`pid`) and multiple equal(1, `sys_file_reference`.`sys_language_uid`) and multiple equal(4079168, `sys_file_reference`.`l10n_parent`) and multiple equal(0, `sys_file_reference`.`deleted`) and multiple equal(0, `sys_file_reference`.`t3ver_wsid`) and multiple equal(0, `sys_file_reference`.`hidden`))" }, { "transformation": "constant_propagation", "resulting_condition": "((multiple equal(0, `sys_file_reference`.`t3ver_oid`) or multiple equal(4, `sys_file_reference`.`t3ver_state`)) and multiple equal(604, `sys_file_reference`.`pid`) and multiple equal(1, `sys_file_reference`.`sys_language_uid`) and multiple equal(4079168, `sys_file_reference`.`l10n_parent`) and multiple equal(0, `sys_file_reference`.`deleted`) and multiple equal(0, `sys_file_reference`.`t3ver_wsid`) and multiple equal(0, `sys_file_reference`.`hidden`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((multiple equal(0, `sys_file_reference`.`t3ver_oid`) or multiple equal(4, `sys_file_reference`.`t3ver_state`)) and multiple equal(604, `sys_file_reference`.`pid`) and multiple equal(1, `sys_file_reference`.`sys_language_uid`) and multiple equal(4079168, `sys_file_reference`.`l10n_parent`) and multiple equal(0, `sys_file_reference`.`deleted`) and multiple equal(0, `sys_file_reference`.`t3ver_wsid`) and multiple equal(0, `sys_file_reference`.`hidden`))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`sys_file_reference`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`sys_file_reference`", "field": "deleted", "equals": "0", "null_rejecting": true }, { "table": "`sys_file_reference`", "field": "l10n_parent", "equals": "4079168", "null_rejecting": true }, { "table": "`sys_file_reference`", "field": "pid", "equals": "604", "null_rejecting": true }, { "table": "`sys_file_reference`", "field": "deleted", "equals": "0", "null_rejecting": true }, { "table": "`sys_file_reference`", "field": "hidden", "equals": "0", "null_rejecting": true }, { "table": "`sys_file_reference`", "field": "l10n_parent", "equals": "4079168", "null_rejecting": true }, { "table": "`sys_file_reference`", "field": "sys_language_uid", "equals": "1", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "`sys_file_reference`", "range_analysis": { "table_scan": { "rows": 5901073, "cost": 6.49118e+06 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "tablenames_fieldname", "usable": false, "cause": "not_applicable" }, { "index": "deleted", "usable": true, "key_parts": [ "deleted", "uid" ] }, { "index": "uid_local", "usable": false, "cause": "not_applicable" }, { "index": "uid_foreign", "usable": false, "cause": "not_applicable" }, { "index": "combined_1", "usable": true, "key_parts": [ "l10n_parent", "t3ver_oid", "t3ver_wsid", "t3ver_state", "deleted", "uid" ] }, { "index": "t3ver_oid", "usable": true, "key_parts": [ "t3ver_oid", "t3ver_wsid", "uid" ] }, { "index": "parent", "usable": true, "key_parts": [ "pid", "deleted", "hidden", "uid" ] }, { "index": "language_identifier", "usable": true, "key_parts": [ "l10n_parent", "sys_language_uid", "uid" ] } ], "setup_range_conditions": [ ], "group_index_skip_scan": { "chosen": false, "cause": "not_group_by_or_distinct" }, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "deleted", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "combined_1", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "t3ver_oid", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "parent", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "language_identifier", "usable": false, "cause": "query_references_nonkey_column" } ] }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "deleted", "ranges": [ "deleted = 0" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "in_memory": 0, "rows": 2950536, "cost": 3.24559e+06, "chosen": true }, { "index": "combined_1", "ranges": [ "l10n_parent = 4079168" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "in_memory": 0, "rows": 33804, "cost": 37185.4, "chosen": true }, { "index": "t3ver_oid", "chosen": false, "cause": "no_valid_range_for_this_index" }, { "index": "parent", "ranges": [ "pid = 604 AND deleted = 0 AND hidden = 0" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "in_memory": 0, "rows": 2140364, "cost": 2.3544e+06, "chosen": false, "cause": "cost" }, { "index": "language_identifier", "ranges": [ "l10n_parent = 4079168 AND sys_language_uid = 1" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "in_memory": 0, "rows": 33286, "cost": 36615.6, "chosen": true } ], "analyzing_roworder_intersect": { "intersecting_indexes": [ { "index": "parent", "index_scan_cost": 3134.77, "cumulated_index_scan_cost": 3134.77, "disk_sweep_cost": 730411, "cumulated_total_cost": 733546, "usable": true, "matching_rows_now": 2.14036e+06, "isect_covering_with_this_index": false, "chosen": true }, { "index": "language_identifier", "index_scan_cost": 49.7335, "cumulated_index_scan_cost": 3184.5, "disk_sweep_cost": 11489.1, "cumulated_total_cost": 14673.6, "usable": true, "matching_rows_now": 12073.1, "isect_covering_with_this_index": false, "chosen": true }, { "index": "deleted", "cumulated_total_cost": 14673.6, "usable": false, "cause": "does_not_reduce_cost_of_intersect" } ], "clustered_pk": { "clustered_pk_added_to_intersect": false, "cause": "no_clustered_pk_index" }, "rows": 12073.1, "cost": 14673.6, "covering": false, "chosen": true } }, "chosen_range_access_summary": { "range_access_plan": { "type": "index_roworder_intersect", "rows": 12073.1, "cost": 14673.6, "covering": false, "clustered_pk_scan": false, "intersect_of": [ { "type": "range_scan", "index": "parent", "rows": 2.14036e+06, "ranges": [ "pid = 604 AND deleted = 0 AND hidden = 0" ] }, { "type": "range_scan", "index": "language_identifier", "rows": 33286, "ranges": [ "l10n_parent = 4079168 AND sys_language_uid = 1" ] } ] }, "rows_for_plan": 12073.1, "cost_for_plan": 14673.6, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`sys_file_reference`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "deleted", "rows": 2.95054e+06, "cost": 885161, "chosen": true }, { "access_type": "ref", "index": "combined_1", "rows": 33804, "cost": 37184.4, "chosen": true }, { "access_type": "ref", "index": "parent", "rows": 2.14036e+06, "cost": 804144, "chosen": false }, { "access_type": "ref", "index": "language_identifier", "rows": 33286, "cost": 36614.6, "chosen": true }, { "rows_to_scan": 12073, "access_type": "range", "range_details": { "used_index": "intersect(parent,language_identifier)" }, "resulting_rows": 12073, "cost": 15880.9, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 12073, "cost_for_plan": 15880.9, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`sys_file_reference`.`hidden` = 0) and (`sys_file_reference`.`t3ver_wsid` = 0) and (`sys_file_reference`.`deleted` = 0) and (`sys_file_reference`.`l10n_parent` = 4079168) and (`sys_file_reference`.`sys_language_uid` = 1) and (`sys_file_reference`.`pid` = 604) and ((`sys_file_reference`.`t3ver_oid` = 0) or (`sys_file_reference`.`t3ver_state` = 4)))", "attached_conditions_computation": [ { "table": "`sys_file_reference`", "rechecking_index_usage": { "recheck_reason": "low_limit", "limit": 1, "row_estimate": 12073, "range_analysis": { "table_scan": { "rows": 5901073, "cost": 6.49118e+06 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "tablenames_fieldname", "usable": false, "cause": "not_applicable" }, { "index": "deleted", "usable": true, "key_parts": [ "deleted", "uid" ] }, { "index": "uid_local", "usable": false, "cause": "not_applicable" }, { "index": "uid_foreign", "usable": false, "cause": "not_applicable" }, { "index": "combined_1", "usable": false, "cause": "not_applicable" }, { "index": "t3ver_oid", "usable": false, "cause": "not_applicable" }, { "index": "parent", "usable": false, "cause": "not_applicable" }, { "index": "language_identifier", "usable": false, "cause": "not_applicable" } ], "setup_range_conditions": [ ], "group_index_skip_scan": { "chosen": false, "cause": "not_group_by_or_distinct" }, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "deleted", "usable": false, "cause": "query_references_nonkey_column" } ] }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "deleted", "ranges": [ "deleted = 0" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "in_memory": 0, "rows": 2950536, "cost": 3.24559e+06, "chosen": true } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "deleted", "rows": 2.95054e+06, "ranges": [ "deleted = 0" ] }, "rows_for_plan": 2.95054e+06, "cost_for_plan": 3.24559e+06, "chosen": true } } } } ], "attached_conditions_summary": [ { "table": "`sys_file_reference`", "attached": "((`sys_file_reference`.`hidden` = 0) and (`sys_file_reference`.`t3ver_wsid` = 0) and (`sys_file_reference`.`deleted` = 0) and (`sys_file_reference`.`l10n_parent` = 4079168) and (`sys_file_reference`.`sys_language_uid` = 1) and (`sys_file_reference`.`pid` = 604) and ((`sys_file_reference`.`t3ver_oid` = 0) or (`sys_file_reference`.`t3ver_state` = 4)))" } ] } }, { "finalizing_table_conditions": [ { "table": "`sys_file_reference`", "original_table_condition": "((`sys_file_reference`.`hidden` = 0) and (`sys_file_reference`.`t3ver_wsid` = 0) and (`sys_file_reference`.`deleted` = 0) and (`sys_file_reference`.`l10n_parent` = 4079168) and (`sys_file_reference`.`sys_language_uid` = 1) and (`sys_file_reference`.`pid` = 604) and ((`sys_file_reference`.`t3ver_oid` = 0) or (`sys_file_reference`.`t3ver_state` = 4)))", "final_table_condition ": "((`sys_file_reference`.`hidden` = 0) and (`sys_file_reference`.`t3ver_wsid` = 0) and (`sys_file_reference`.`deleted` = 0) and (`sys_file_reference`.`l10n_parent` = 4079168) and (`sys_file_reference`.`sys_language_uid` = 1) and (`sys_file_reference`.`pid` = 604) and ((`sys_file_reference`.`t3ver_oid` = 0) or (`sys_file_reference`.`t3ver_state` = 4)))" } ] }, { "refine_plan": [ { "table": "`sys_file_reference`", "pushed_index_condition": "(`sys_file_reference`.`deleted` = 0)", "table_condition_attached": "((`sys_file_reference`.`hidden` = 0) and (`sys_file_reference`.`t3ver_wsid` = 0) and (`sys_file_reference`.`l10n_parent` = 4079168) and (`sys_file_reference`.`sys_language_uid` = 1) and (`sys_file_reference`.`pid` = 604) and ((`sys_file_reference`.`t3ver_oid` = 0) or (`sys_file_reference`.`t3ver_state` = 4)))" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.00 sec)> 9. Is the slow case the one where no translation exists for the record?
A translation exists for the record.
> 10. Did the table ever get restored from a dump or copied between servers without running ANALYZE TABLE afterwards?
No. We migrate the whole platform a while ago, but we a mysqldump.
Also, optimize is run weekly. -
Wolfgang Klinger commented <span data-processor="relativetime" data-value="2026-10-10T05:01:12+00:00" > 10/10/26, 05:01 </span>
@dogawaf
Thanks, the optimizer trace explains it.
You have a data issue. Your index shows about 33,000 rows with l10n_parent = 4079168, and 1.1 million rows on pid 604. One default record with 33k translations means something on your side creates duplicate translation rows instead of updating them(?).
With that data shape I can reproduce the plan on MySQL 8.4. Because of LIMIT 1, MySQL rechecks the index choice (see “recheck_reason”: “low_limit” in your trace). It assumes it will find a match after reading just a few rows of the deleted index, so that plan looks almost free. With your data it reads millions of rows instead.
Dropping the deleted index does not help. MySQL then does a full table scan in the same recheck.
Your EXPLAIN for “without LIMIT 1” still has LIMIT 1 in it. Without it MySQL uses an index intersection, about 200 ms in my test.
TYPO3 13.4 and main don’t use LIMIT 1 for this query anymore, so supported versions are not affected. v12 is no longer maintained. I’d fix the duplicate translations and keep your local optimizer hint until you upgrade.
Nothing to fix in core here, so I’d close this issue.
-
Garvin Hicking commented and changed status
Needs FeedbackClosed <span data-processor="relativetime" data-value="2026-10-10T11:31:24+00:00" > 10/10/26, 11:31 </span>Closing for now
-
Rémy DANIEL commented <span data-processor="relativetime" data-value="2026-10-12T07:42:05+00:00" > 10/12/26, 07:42 </span>
@wazum
Thank you for all the analysis and explanation. I will now try to find out where the duplicate rows come from.
For the record, here is the explain of the query with and without LIMIT 1, responding to your question 7:
mysql> explain SELECT * FROM `sys_file_reference` -> WHERE (`pid` = 604) AND (`sys_language_uid` = 1) -> AND (`l10n_parent` = 4079168) -> AND (((`sys_file_reference`.`deleted` = 0) -> AND (((`sys_file_reference`.`t3ver_wsid` = 0) -> AND (((`sys_file_reference`.`t3ver_oid` = 0) OR (`sys_file_reference`.`t3ver_state` = 4))))) -> AND (`sys_file_reference`.`hidden` = 0))) LIMIT 1; +----+-------------+--------------------+------------+-------+---------------------------------------------------------+---------+---------+------+---------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------------+------------+-------+---------------------------------------------------------+---------+---------+------+---------+----------+------------------------------------+ | 1 | SIMPLE | sys_file_reference | NULL | range | deleted,combined_1,t3ver_oid,parent,language_identifier | deleted | 2 | NULL | 2935078 | 0.00 | Using index condition; Using where | +----+-------------+--------------------+------------+-------+---------------------------------------------------------+---------+---------+------+---------+----------+------------------------------------+ 1 row in set, 1 warning (0.13 sec)mysql> explain SELECT * FROM `sys_file_reference` WHERE (`pid` = 604) AND (`sys_language_uid` = 1) AND (`l10n_parent` = 4079168) AND (((`sys_file_reference`.`deleted` = 0) AND (((`sys_file_reference`.`t3ver_wsid` = 0) AND (((`sys_file_reference`.`t3ver_oid` = 0) OR (`sys_file_reference`.`t3ver_state` = 4))))) AND (`sys_file_reference`.`hidden` = 0))); +----+-------------+--------------------+------------+-------------+---------------------------------------------------------+----------------------------+---------+------+-------+----------+----------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------------+------------+-------------+---------------------------------------------------------+----------------------------+---------+------+-------+----------+----------------------------------------------------------+ | 1 | SIMPLE | sys_file_reference | NULL | index_merge | deleted,combined_1,t3ver_oid,parent,language_identifier | parent,language_identifier | 8,8 | NULL | 12136 | 10.00 | Using intersect(parent,language_identifier); Using where | +----+-------------+--------------------+------------+-------------+---------------------------------------------------------+----------------------------+---------+------+-------+----------+----------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
@dogawaf
I was not able to reproduce this locally in a fresh MySQL 8.4 database with 2.8 million rows in
sys_file_reference(200k translations, all core indexes present). The optimizer always chosecombined_1, with or withoutLIMIT 1, and also with stale or empty index statistics.1. Which exact MySQL version is it, and is it plain MySQL or a managed service?
2. Please paste the output of
SHOW CREATE TABLE sys_file_reference. Where does thelanguage_identifierindex come from?3. How many rows does the table have, how many have
sys_language_uid > 0, and how many havepid = 604?4. Please paste
SELECT * FROM mysql.innodb_index_stats WHERE table_name = 'sys_file_reference'.5. Please paste the values of
innodb_stats_persistent,innodb_stats_auto_recalcandinnodb_stats_persistent_sample_pages.6. Run
ANALYZE TABLE sys_file_reference, then runEXPLAINon the slow query again. Does the key column still say deleted?7. Run
EXPLAINon the same query withoutLIMIT 1. Does the key column still say deleted?8. Please run the query with
EXPLAIN ANALYZEand with the optimizer trace enabled, and attach both. The trace shows why each index was rejected:9. Is the slow case the one where no translation exists for the record?
10. Did the table ever get restored from a dump or copied between servers without running
ANALYZE TABLEafterwards?