In MW 1.11, rSVN23917: *Include usertext_timestamp index here and rSVN23491: *Add usertext_timestamp index to archive table added a usertext_timestamp index to archive.
In MW 1.15, a series of patches affected this index:
- rSVN45764: For backport to 1.14. renamed it to ar_usertext_timestamp.
- rSVN45769: * Preserve usertext_timestamp index name for MySQL, prefix it for SQLite. added the /*i*/ thing so MySQL would pretend that /*i*/ar_usertext_timestamp actually meant usertext_timestamp.
- rSVN45819: Attempt at cleaning up r45764, r45769 (renamed indices for SQLite compat): undid the /*i*/ thing, trying to do things a different way. rSVN45891: Revert r45821, r45830, r45831 and r45835 all in order to revert r45819, per… reverted that.
It looks like after all of that the result was that even though tables.sql said /*i*/ar_usertext_timestamp, the installer would create the index named usertext_timestamp. But if you create the tables directly from tables.sql, you're liable to wind up with ar_usertext_timestamp instead. See T104756.
In MW 1.28, rMWbec6151e5fe3: Remove old remapping hacks from Database::indexName() tried removing the /*i*/-aliasing. That wound up being reverted in rMW307851618430: Revert "Remove old remapping hacks from Database::indexName()". Also, rMWc13dd55f4e04: Fix incorrect index name in new 1.28.0 installations created a MySQL-specific patch-rename-ar_usertext_timestamp.sql to try to rename ar_usertext_timestamp to usertext_timestamp, but as far as I can tell it'll never actually be run by update.php because patch-archive-user-index.sql from MW 1.11 (rSVN23917) will create usertext_timestamp first.
In MW 1.31, rMW4ccb228bde92: rdbms: inject the mysql index name aliases into Database removed the hard-coding of the /*i*/ overrides from DatabaseMySQLBase, instead injecting them from MWLBFactory. But the installer doesn't use MWLBFactory, so now on installation it'll create ar_usertext_timestamp. The first time update.php is run, the patch from 1.11 will run to add usertext_timestamp. Apparently no one filed a bug about this breaking anything, or they just decided it was a duplicate of T104756.
In MW 1.34, rMWc29909e59fd8: Mostly drop old pre-actor user schemas drops /*i*/ar_usertext_timestamp, which currently actually drops usertext_timestamp. ar_usertext_timestamp, if it exists, isn't dropped. When the ar_user_text column is dropped, MySQL apparently just goes ahead and silently changes the index from (ar_user_text, ar_timestamp) to just (ar_timestamp). Grr.
At some point (e.g. now) we'll realize that the index alias in MWLBFactory isn't being used anymore and we'll delete it, at which point it'll try to drop ar_usertext_timestamp (possibly causing an error if that doesn't exist) but leave usertext_timestamp in place.