Issue Task Rejected

#111007Remove the standalone "deleted" index from sys_file_reference

Wolfgang Klinger 10/10/26, 05:06 updated 10/14/26, 16:34

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_reference by deleted also filters by pid (frontend overlays, recycler, cleanup:deletedrecords). The auto-generated parent index (pid, deleted, hidden) covers all of them. combined_1 also ends with deleted.
  • 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

  1. Wolfgang Klinger changed status Under Review Rejected priority Should have Won't have this time <span data-processor="relativetime" data-value="2026-10-14T16:34:47+00:00" > 10/14/26, 16:34 </span>