flow_wiki_ref has 2 indices: flow_wiki_ref_idx_v2 & flow_wiki_ref_revision_v2
flow_ext_ref has 2 indices: flow_ext_ref_idx_v2 & flow_ext_ref_revision_v2
I believe the flow_*_ref_revision_v2 indices in both of those tables are useless.
The only queries to these columns I could find are in:
- ReferenceRecorder::getExistingReferences
- LinksTableUpdater::getReferencesForTitle
- ReferenceClarifier::loadReferencesForPage
That query seems useless to all of those.
I assume it was originally created to ensure uniqueness, but we've had to drop that unique constraint in the past.
I believe it would be safe to drop it.
Looking at the queries that happen: ReferenceRecorder::getExistingReferences doesn't seem to be able to benefit much from any index. It'll query WHERE ref_src_wiki = ... AND ref_src_object_type = ... AND ref_src_object_id = ....
It's probably not an issue now because those tables are still small enough, but we probably need to optimize for that query.