Viewing recent changes with debug logging on shows loads of queries like:
[queries] wiki: SELECT /* Linker::getRollbackEditCount Admin */ rev_user_text,rev_deleted FROM `revision` FORCE INDEX (page_timestamp) WHERE rev_page = '106' ORDER BY rev_timestamp DESC LIMIT 11
These should use some sort of batching. I guess it would have to use subqueries though. Another option would be to cache it and use getMulti() since the query is just getting the last 11 revs of a page (see T122546).
The query reads the 11 newest edit for a page.
Subqueries does not allow a LIMIT (it needs another subquery to apply limit, but in that case the outer rev_page cannot be used in the limit subquery, so a limit per page seems not possible)
It could use window function with ROW_NUMBER() OVER ( PARTITION BY rev_page ORDER BY rev_timestamp DESC, rev_id DESC ) as row_num, but that needs another subquery to apply row_num <= 11 and that makes the explain worse (as a intermediate result is created with all rows of the pages before row number could be filtered). But the size of the result set would be up to 11 * batch size.
The application code is processing the result set to count per page and filter with rev_deleted, but moving this code into the query brings to much business logic into the database.
Suggest to keep this query and not use batching (declining this task). Using cache is discussed at T122546
select rev_page, rev_timestamp, rev_id, rev_actor, rev_deleted from revision where rev_id in ( select rev_id from ( select ROW_NUMBER() OVER ( PARTITION BY rev_page ORDER BY rev_timestamp DESC, rev_id DESC ) as row_num , rev_id from revision where rev_page in ( 1, 2 ) ) as row_query where row_num <= 11 and rev_page in ( 1, 2 ) ) ORDER BY rev_page , rev_timestamp DESC , rev_id DESC
Getting count, also getting rev_deleted to check bit in code
with revision_limited_row as ( select row_num, rev_page, rev_actor, rev_deleted from ( select ROW_NUMBER() OVER ( PARTITION BY rev_page ORDER BY rev_timestamp DESC, rev_id DESC ) as row_num , rev_page , rev_actor , rev_deleted from revision where rev_page in ( 1, 2 ) ) as row_query WHERE row_num <= 11 and rev_page in ( 1, 2 ) ), revision_row_stop as ( select r.rev_page, min( row_num ) as row_num from revision_limited_row r where r.rev_actor != ( select i.rev_actor from revision i inner join page on i.rev_id = page_latest where page_id = r.rev_page ) group by r.rev_page ), revision_rev_deleted as ( select r.rev_page, r.rev_deleted from revision_limited_row r where r.row_num = ( select s.row_num from revision_row_stop s where s.rev_page = r.rev_page ) group by r.rev_page ) select rev_page, count(*) as rollback_count, ( select rev_deleted d from revision_rev_deleted d where d.rev_page = r.rev_page ) as rev_deleted from revision_limited_row r where r.rev_page in ( 1, 2 ) and r.row_num < ( select s.row_num from revision_row_stop s where s.rev_page = r.rev_page ) GROUP BY r.rev_page