On Mon, Jun 3, 2019 at 4:30 AM Bryan Davis bd808@wikimedia.org wrote:
One "fix" I could imagine for this would be for the Wiki Replicas to also provide per-entity views of the comment table that only return rows related to a single entity table. For example a "comment_ipblocks" view could use a where clause specific to the ipblocks entity table like "WHERE exists( select 1 from ipblocks where ipb_reason_id = comment_id and ipb_deleted = 0)" and exclude the tests for other entities (image, filearchive, revision, etc).
We have created new views to do this single entity table filtering for both the actor and comment tables. See https://wikitech.wikimedia.org/wiki/News/Actor_storage_changes_on_the_Wiki_Replicas#The_actor_table_seems_really_slow--so_does_comment for more information.
TL;DR: * actor_filearchive * actor_image * actor_ipblocks * actor_logging * actor_oldimage * actor_protected_titles * actor_recentchanges * actor_revision
* comment_filearchive * comment_image * comment_ipblocks * comment_logging * comment_oldimage * comment_protected_titles * comment_recentchanges * comment_revision
Bryan