I cross checked with a random replica in enwiki and took the intersection.

The biggest impact can be made by dropping cl_collation_ext. We need to make sure updateCollation.php maint script doesn't need it (or modify it in a way that wouldn't need to use it)

We should check that in production and with different types of hosts: main traffic, API and vslow, just in case.
While the replicas are good as an indication, we've had issues with production hosts, especially because of the optimizer gathered stats.

In T342697#9045614, @Ladsgroup wrote:

The biggest impact can be made by dropping cl_collation_ext.

I suspect this may be related to ICU updates for collation, which we do very rarely, but when they happen, they're quite important. E.g. annually around PHP upgrades.

• (2020) RemoteIcuCollation: T263437: Allow easier ICU transitions in MediaWiki (change how sortkey collation is managed in the categorylinks table)
• (2016) updateCollation.php and this index: T58041, https://gerrit.wikimedia.org/r/c/mediawiki/core/+/272416

Yeah I know about ICU updates (and some plans on improving it (T329491: ICU transition towards ICU 67) but the maint script can simply iterate through cl_from in batches of 1000 values (cl_from between x and x+1000) or something like that without needing to rely on an index.

In T342697#9045678, @Marostegui wrote:

We should check that in production and with different types of hosts: main traffic, API and vslow, just in case.
While the replicas are good as an indication, we've had issues with production hosts, especially because of the optimizer gathered stats.

Definitely, also this is a starting point. I'm checking in code to make sure we don't end up with another tmp1 incident, for example. the index mentioned on user (user_email_token) is actually used when you search for the columns being indexed but it's used quite rarely that's not showing up in used indexes but I'm not gonna drop it.

For the MediaModeration 2.0 work we will probably use the oi_sha1 index to search for old images by their sha1.

Change 994154 had a related patch set uploaded (by Dreamy Jazz; author: Dreamy Jazz):

[mediawiki/extensions/CheckUser@master] Make it possible to find in use DB indexes via a code search

https://gerrit.wikimedia.org/r/994154

Change 994154 merged by jenkins-bot:

[mediawiki/extensions/CheckUser@master] Make it possible to find in use DB indexes via a code search

https://gerrit.wikimedia.org/r/994154