TYPO3 Core

pages has a key on (pid,deleted,sorting) that typically kicks in and drastically reduces the result set. this was researched quite often already.

same for tt_content, the typically used key is KEY parent (pid,sorting) that reduces the result set to elements on a specific page. with a typical set of < 100 elements on one page a manual scan of the result is then quicker than maintaining further indexes.

did you measure anything, yet, or is this issue just a "i thought it might be wise?". which exact queries do you refer to? in short, new indexes will only be accepted for typical use cases that really lead to a significant benefit for a majority of sites.

Closing this as adding the key on deleted and hidden alone does not provide any value since most queries also restrict on either UID or PID.

In the PID case the parent index gets used as it eliminates most other records due to pid and deleted being indexes.
In the UID case it's the primary key which already reduces the number of queried rows to 1.

Adding and Index on pid, deleted and hidden doesn't provide a noticeable benefit either since the biggest part of pages will already be filtered by pid and deleted, so it's down to the hidden field, but that's just a few rows that need comparing which isn't noticeably improved by the index.

If you think that this is the wrong decision or experience this issue again, then please get into contact on slack, write to the mailing list typo3.teams.bugs with issue number and an explanation or open a new ticket and add a relation to this ticket number.