While doing T205094: Investigate and restructure SQL directory, and use new MediaWiki abstract schema change features I realized structure of wb_changes is this:
CREATE TABLE IF NOT EXISTS /*_*/wb_changes ( change_id INT unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, -- Id of change change_type VARCHAR(25) NOT NULL, -- Type of the change change_time varbinary(14) NOT NULL, -- Time the change was made change_object_id varbinary(14) NOT NULL, -- The full id of the object (ie item, query) the change affects change_revision_id INT unsigned NOT NULL, -- The id of the revision on the repo that made the change change_user_id INT unsigned NOT NULL, -- The id of the user on the repo that made the change change_info MEDIUMBLOB NOT NULL -- Holds additional info about the change, inc diff and stuff ) /*$wgDBTableOptions*/; CREATE INDEX /*i*/wb_changes_change_type ON /*_*/wb_changes (change_type); CREATE INDEX /*i*/wb_changes_change_time ON /*_*/wb_changes (change_time); CREATE INDEX /*i*/wb_changes_change_object_id ON /*_*/wb_changes (change_object_id); CREATE INDEX /*i*/wb_changes_change_user_id ON /*_*/wb_changes (change_user_id); CREATE INDEX /*i*/wb_changes_change_revision_id ON /*_*/wb_changes (change_revision_id);
It seems all of indexes are complete random sense of "each column will have an index" (because why not). I'm pretty sure most of these indexes are not used at all and we can drop them. wb_changes is rather small but it's a pretty heavy-write table (twice of ratio of edits) so cleaning up the indexes would make a big difference in replication (also we can turn the ones that are used to covering index for faster read/write)
I add our DBAs to check if they can confirm the hypotheses. This table only exists on s8.