I looked through the 861 (!) SQL queries that are issued when I visit Special:Contributions/Catrope on my localhost (39 Flow lines out of 50 total), and found the following excessive queries:
- 118 calls (3 per rendered row?) calls to SELECT * FROM flow_tree_revision JOIN flow_revision rev ON ((tree_rev_id = rev_id)) WHERE rev_type_id = 'X' AND rev_type = 'Y' ORDER BY rev_id DESC LIMIT 100 is called once for every rendered revision and for every current revision by RevisionStorage, called indirectly from AbstractQuery::loadMetadataBatch(). This query occurs 118 times on my localhost (probably 3 queries per rendered row?).
- 85 calls to SELECT page_namespace,page_title,page_id,page_len,page_is_redirect,page_latest,page_content_model FROM page WHERE page_id = 'N' LIMIT 1, even many times for the same page. These calls come from $result->workflow->isDeleted() (which calls Title::newFromID()) in the loop in ContributionsQuery::getResults(). The problem appears to be that while there is an existence cache for titles by name, there isn't one by ID.
- While formatting each row (RevisionFormatter::formatApi()), RevisionActionPermissions::isAllowed() is called. This results in several queries:
- SELECT * FROM flow_tree_revision JOIN flow_revision rev ON ((tree_rev_id = rev_id)) WHERE rev_type_id = 'X' AND rev_type = 'Y' ORDER BY rev_id DESC LIMIT 100 from `RevisionActionPermissions::getRoot()
- Sometimes that same query but with a different type_id is also called from CollectionCache::getLastRevisionFor(), but this doesn't happen for every row
- SELECT * FROM flow_topic_list WHERE topic_id = 'X' LIMIT 1 from AbstractCollection::getBoardWorkflow()
- SELECT page_namespace,page_title,page_id,page_len,page_is_redirect,page_latest,page_content_model FROM page WHERE page_id = 'N' LIMIT 1 from Workflow::isDeleted()
- RevisionFormatter::buildActions() then proceeds to call Workflow::isDeleted() a bunch more times (indirectly, , resulting in another ~33 Title::newFromId() queries
More stuff happens after that that I didn't bother to narrate. In total, I saw 337 Title::newFromId queries, 266 RevisionStorage::findInternal queries and 187 BasicDbStorage::find (flow_topic_list) queries, accounting for 790 of the 861 queries on this page. In production, this isn't as much of a problem because of the indexes, but we're talking about getting rid of those. With proper batching and in-process caching, we should be able to eliminate hundreds of queries.