Looking at the latest source code, it seems the index forcing is still present there as well. It may be worth to consider removing it if we can verify it doesn't hurt the performance on MariaDB.

The query, along with the index forcing, was added in rMWa53fe6f080a2dfe48ed77c09af9e09ef37085408 and not really modified since.

Tagging Platform as potential stakeholders and @hoo as the original author of the query :)

The query also performs well with the index forcing if I get rid of the JOINs on the comment tables, since they are never actually used by this query.

Looking in tendril for slow queries that has this index forced says that there are some queries that are slow for us too and become way faster once you remove the forced index. Here's an example:

SELECT /* IndexPager::buildQueryInfo (history page unfiltered) */ rev_id, rev_page, rev_timestamp, rev_minor_edit, rev_deleted, rev_len, rev_parent_id, rev_sha1, comment_rev_comment.comment_text AS `rev_comment_text`, comment_rev_comment.comment_data AS `rev_comment_data`, comment_rev_comment.comment_id AS `rev_comment_cid`, actor_rev_user.actor_user AS `rev_user`, actor_rev_user.actor_name AS `rev_user_text`, temp_rev_user.revactor_actor AS `rev_actor`, user_name, (SELECT GROUP_CONCAT(ctd_name SEPARATOR ', ') FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id)) WHERE ct_rev_id=rev_id ) AS `ts_tags` FROM `revision` FORCE INDEX (page_timestamp) JOIN `revision_comment_temp` `temp_rev_comment` ON ((temp_rev_comment.revcomment_rev = rev_id)) JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = temp_rev_comment.revcomment_comment_id)) JOIN `revision_actor_temp` `temp_rev_user` ON ((temp_rev_user.revactor_rev = rev_id)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor)) LEFT JOIN `user` ON ((actor_rev_user.actor_user != 0) AND (user_id = actor_rev_user.actor_user)) WHERE rev_page = 702579 ORDER BY rev_timestamp DESC, rev_id DESC LIMIT 5001 /* c47cac4131b0f472ea37f87011a99294 db1084 enwiki 2s */

(Takes 2.76s with forced index and 0.21s without it). cc @Marostegui

Interestingly, https://www.mediawiki.org/wiki/Manual:$wgShowRollbackEditCount suggests that $wgMiserMode can be used to disable this counting of edits for rollback links rendering, but the code seems to perform the calculation anyways (in \Linker::generateRollback).

In T260997#6402702, @Ladsgroup wrote:

Looking in tendril for slow queries that has this index forced says that there are some queries that are slow for us too and become way faster once you remove the forced index. Here's an example:

SELECT /* IndexPager::buildQueryInfo (history page unfiltered) */ rev_id, rev_page, rev_timestamp, rev_minor_edit, rev_deleted, rev_len, rev_parent_id, rev_sha1, comment_rev_comment.comment_text AS `rev_comment_text`, comment_rev_comment.comment_data AS `rev_comment_data`, comment_rev_comment.comment_id AS `rev_comment_cid`, actor_rev_user.actor_user AS `rev_user`, actor_rev_user.actor_name AS `rev_user_text`, temp_rev_user.revactor_actor AS `rev_actor`, user_name, (SELECT GROUP_CONCAT(ctd_name SEPARATOR ', ') FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id)) WHERE ct_rev_id=rev_id ) AS `ts_tags` FROM `revision` FORCE INDEX (page_timestamp) JOIN `revision_comment_temp` `temp_rev_comment` ON ((temp_rev_comment.revcomment_rev = rev_id)) JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = temp_rev_comment.revcomment_comment_id)) JOIN `revision_actor_temp` `temp_rev_user` ON ((temp_rev_user.revactor_rev = rev_id)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor)) LEFT JOIN `user` ON ((actor_rev_user.actor_user != 0) AND (user_id = actor_rev_user.actor_user)) WHERE rev_page = 702579 ORDER BY rev_timestamp DESC, rev_id DESC LIMIT 5001 /* c47cac4131b0f472ea37f87011a99294 db1084 enwiki 2s */

(Takes 2.76s with forced index and 0.21s without it). cc @Marostegui

I cannot reproduce it. On db1089 (enwiki) the FORCE still works better, 0.2 secs vs 3.1 secs (this is 10.1).
On 10.4 (db1084) it is the same.

We're happy to look at a patch, or give advice if needed, but we don't see much for us in this ticket. Feel free to re-tag us if you need our help.

@TK-999 I'm very interested in the Percona aspect of this. Have you tried my Percona driver ??

I doubt it would solve this problem, but I'm curious about how you're approaching the different index handling.

In T260997#6410559, @MarkAHershberger wrote:

@TK-999 I'm very interested in the Percona aspect of this. Have you tried my Percona driver ??

I doubt it would solve this problem, but I'm curious about how you're approaching the different index handling.

Thanks for the link! We're not using Percona XtraDB, though, but rather Percona Server which is basically just Oracle's MySQL with additional fixes.

After some investigation, this problem may be simply down to index stats not being recalculated on certain replicas after reimporting a wiki DB during the upgrade process, so I think I am going to close this ticket as not reproducible unless the issue rears its head again.