PRIMARY KEY (afh_id),
KEY (afh_filter),
KEY (afh_user),
KEY (afh_user_text),
KEY (afh_timestamp)

Remarks:

• Special:AbuseFilter/history uses paging on afh_timestamp but this isn't a unique index (which is recommended).
• afh_user is probably only used for efficient renaming users (implemented recently). If not, it can be removed.
• afh_user_text is used for filtering Special:AbuseFilter/history by user who changed a filter. However, the page is also ordered by timestamp, so it should probably be (afh_user_text, afh_timestamp).
• afh_filter is used for filtering Special:AbuseFilter/history by changed filter. Again, for ordering and paging, it should be (afh_filter, afh_timestamp). It is also used for retrieving the afh_id of the next/previous change to the same filter, so (afh_filter, afh_id) is another index to consider.
  • Additionally, Special:AbuseFilter/history can be filtered by both filter AND user at once. Efficient indices for this would be (afh_filter, afh_user_text, afh_timestamp) or (afh_user_text, afh_filter, afh_timestamp)

This could be done as part of T188180, where afh_user and afh_user_text would be merged to afh_actor. Or T227595 which would migrate this versioning to revision in core.