While troubleshooting T202764 we discovered there are three indexes placed on the recentchanges table called:
KEY `tmp_1` (`rc_this_oldid`), KEY `tmp_2` (`rc_bot`,`rc_timestamp`), KEY `tmp_3` (`rc_namespace`,`rc_timestamp`),
tmp1 is going to be addressed with: T202167: Schema change for rc_this_oldid index - it is quite messy as it doesn't exist everywhere. Example: exists on enwiki hosts but not on commons
However, tmp2 and tmp3 there is no trace of those on tables.sql. They are most likely indexes that were added on the fly to mitigate something and then it was forgotten to:
- be added to tables.sql
- get them added with a proper name
tmp2 and tmp3 exist mostly, on all the hosts, but not everywhere, not even within the same section. It is a mess.
Example for enwiki: they exist everywhere but on db1105 (one of the recentchanges slave), db1106 (one of the api hosts), db1067 (enwiki master)
Example for commonswiki: exists everywhere
Example for wikidatawiki: Only exists on recentchanges slaves (db1101 and db1099) and the master (db1071)
Example for eswiki: Exists everywhere
Example for dewiki: Exists almost everywhere but not on: the master (db1070), db1110 (main traffic), db1113 (vslow), db1082 (api+main)
A quick check on enwiki checks (using sys.schema_unused_indexes) doesn't report that any of those indexes are unused, so I guess some queries are using them,. I guess not in a very dramatic way as otherwise we'd have seen issues on the slaves that doesn't have them (on enwiki and wikidatawiki)?
Any thoughts on how to move forward? What I can think of:
- Add them with a proper name to tables.sql and drop+create on production with the proper name (and create them where they don't exist)
- Just drop them in production and make production the same as tables.sql