#111007Remove the standalone "deleted" index from sys_file_reference
Description
Problem
sys_file_reference has a single-column index KEY deleted (deleted) in typo3/sysext/core/ext_tables.sql. It was added in 2014 with #54725. No other core table has an index like this.
The index is useless:
- A one-column index on a flag that is 0 for almost every row has no selectivity. The optimizer never picks it for a normal lookup.
- Every core query that filters
sys_file_referencebydeletedalso filters bypid(frontend overlays, recycler,cleanup:deletedrecords). The auto-generatedparentindex (pid, deleted, hidden) covers all of them.combined_1also ends withdeleted. - Nothing in core refers to the index by name.
It still costs a B-tree that is updated on every insert, update and delete of a file reference. On a large table that is several thousand pages.
Solution
Remove the KEY deleted (deleted) line from ext_tables.sql.
New installations will not get the index. Existing installations keep it until the “remove” suggestion in the Database Analyzer is applied.
Reviews 1
History 23
-
Wolfgang Klinger changed assigned to Wolfgang Klinger <span data-processor="relativetime" data-value="2026-10-10T05:06:52+00:00" > 10/10/26, 05:06 </span>
-
Gerrit Code Review pushed patch set 1 for main to change 96585 status
NewUnder Review <span data-processor="relativetime" data-value="2026-10-10T05:08:43+00:00" > 10/10/26, 05:08 </span> -
Wolfgang Klinger changed status
Under ReviewRejected priorityShould haveWon't have this time <span data-processor="relativetime" data-value="2026-10-14T16:34:47+00:00" > 10/14/26, 16:34 </span>