In order to make this easier to find/read I am creating a subtask of this query regression I have noticed on 10.6 (which is big enough to have its own task). This is created from T301879#7799599
Upstream bug: https://jira.mariadb.org/browse/MDEV-28155
```
SELECT rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,page_latest,(SELECT GROUP_CONCAT(ctd_name SEPARATOR ',') FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id)) WHERE ct_rc_id=rc_id ) AS `ts_tags`,fp_stable,fp_pending_since,ores_damaging_cls.oresc_probability AS `ores_damaging_score`,ores_goodfaith_cls.oresc_probability AS `ores_goodfaith_score` FROM `recentchanges` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) LEFT JOIN `flaggedpages` ON ((fp_page_id = rc_cur_id)) LEFT JOIN `ores_classification` `ores_damaging_cls` ON (ores_damaging_cls.oresc_model = 59 AND (ores_damaging_cls.oresc_rev=rc_this_oldid) AND ores_damaging_cls.oresc_class = 1) LEFT JOIN `ores_classification` `ores_goodfaith_cls` ON (ores_goodfaith_cls.oresc_model = 60 AND (ores_goodfaith_cls.oresc_rev=rc_this_oldid) AND ores_goodfaith_cls.oresc_class = 1) WHERE (actor_user IS NOT NULL) AND rc_bot = 0 AND (rc_minor = 0) AND ((rc_this_oldid = page_latest) OR rc_type = 3) AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace = 0) AND (rc_timestamp >= '20220316104610') AND rc_new IN (0,1) ORDER BY rc_timestamp DESC LIMIT 500;
```
10.4 picks `rc_timestamp` and 10.6 picks `rc_name_type_patrolled_timestamp`. Which is faster: 0.6 vs 4 seconds.
```
root@db1132.eqiad.wmnet[enwiki]> explain SELECT rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,page_latest,(SELECT GROUP_CONCAT(ctd_name SEPARATOR ',') FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id)) WHERE ct_rc_id=rc_id ) AS `ts_tags`,fp_stable,fp_pending_since,ores_damaging_cls.oresc_probability AS `ores_damaging_score`,ores_goodfaith_cls.oresc_probability AS `ores_goodfaith_score` FROM `recentchanges` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) LEFT JOIN `flaggedpages` ON ((fp_page_id = rc_cur_id)) LEFT JOIN `ores_classification` `ores_damaging_cls` ON (ores_damaging_cls.oresc_model = 59 AND (ores_damaging_cls.oresc_rev=rc_this_oldid) AND ores_damaging_cls.oresc_class = 1) LEFT JOIN `ores_classification` `ores_goodfaith_cls` ON (ores_goodfaith_cls.oresc_model = 60 AND (ores_goodfaith_cls.oresc_rev=rc_this_oldid) AND ores_goodfaith_cls.oresc_class = 1) WHERE (actor_user IS NOT NULL) AND rc_bot = 0 AND (rc_minor = 0) AND ((rc_this_oldid = page_latest) OR rc_type = 3) AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace = 0) AND (rc_timestamp >= '20220316104610') AND rc_new IN (0,1) ORDER BY rc_timestamp DESC LIMIT 500;
+------+--------------------+---------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------------------------+---------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+---------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------------------------+---------+----------------------------------------------------+
| 1 | PRIMARY | recentchanges | ref | rc_timestamp,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_this_oldid,rc_new_name_timestamp | rc_name_type_patrolled_timestamp | 4 | const | 4808652 | Using index condition; Using where; Using filesort |
| 1 | PRIMARY | page | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.recentchanges.rc_cur_id | 1 | Using where |
| 1 | PRIMARY | flaggedpages | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.recentchanges.rc_cur_id | 1 | |
| 1 | PRIMARY | ores_damaging_cls | eq_ref | oresc_rev_model_class | oresc_rev_model_class | 7 | enwiki.recentchanges.rc_this_oldid,const,const | 1 | |
| 1 | PRIMARY | ores_goodfaith_cls | eq_ref | oresc_rev_model_class | oresc_rev_model_class | 7 | enwiki.recentchanges.rc_this_oldid,const,const | 1 | |
| 1 | PRIMARY | recentchanges_actor | eq_ref | PRIMARY,actor_user | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | Using where |
| 1 | PRIMARY | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_comment_id | 1 | |
| 2 | DEPENDENT SUBQUERY | change_tag | ref | ct_rc_tag_id,ct_tag_id_id | ct_rc_tag_id | 5 | enwiki.recentchanges.rc_id | 1 | Using index |
| 2 | DEPENDENT SUBQUERY | change_tag_def | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.change_tag.ct_tag_id | 1 | |
+------+--------------------+---------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------------------------+---------+----------------------------------------------------+
9 rows in set (0.002 sec)
mysql:root@localhost [enwiki]> explain SELECT rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,page_latest,(SELECT GROUP_CONCAT(ctd_name SEPARATOR ',') FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id)) WHERE ct_rc_id=rc_id ) AS `ts_tags`,fp_stable,fp_pending_since,ores_damaging_cls.oresc_probability AS `ores_damaging_score`,ores_goodfaith_cls.oresc_probability AS `ores_goodfaith_score` FROM `recentchanges` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) LEFT JOIN `flaggedpages` ON ((fp_page_id = rc_cur_id)) LEFT JOIN `ores_classification` `ores_damaging_cls` ON (ores_damaging_cls.oresc_model = 59 AND (ores_damaging_cls.oresc_rev=rc_this_oldid) AND ores_damaging_cls.oresc_class = 1) LEFT JOIN `ores_classification` `ores_goodfaith_cls` ON (ores_goodfaith_cls.oresc_model = 60 AND (ores_goodfaith_cls.oresc_rev=rc_this_oldid) AND ores_goodfaith_cls.oresc_class = 1) WHERE (actor_user IS NOT NULL) AND rc_bot = 0 AND (rc_minor = 0) AND ((rc_this_oldid = page_latest) OR rc_type = 3) AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace = 0) AND (rc_timestamp >= '20220316104610') AND rc_new IN (0,1) ORDER BY rc_timestamp DESC LIMIT 500;
+------+--------------------+---------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+------------------------------------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+---------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+------------------------------------------------+---------+-------------+
| 1 | PRIMARY | recentchanges | range | rc_timestamp,rc_name_type_patrolled_timestamp,rc_namespace_title_timestamp,rc_ns_actor,rc_actor,rc_this_oldid,rc_new_name_timestamp | rc_timestamp | 14 | NULL | 4502872 | Using where |
| 1 | PRIMARY | page | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.recentchanges.rc_cur_id | 1 | Using where |
| 1 | PRIMARY | flaggedpages | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.recentchanges.rc_cur_id | 1 | |
| 1 | PRIMARY | ores_damaging_cls | eq_ref | oresc_rev_model_class | oresc_rev_model_class | 7 | enwiki.recentchanges.rc_this_oldid,const,const | 1 | |
| 1 | PRIMARY | ores_goodfaith_cls | eq_ref | oresc_rev_model_class | oresc_rev_model_class | 7 | enwiki.recentchanges.rc_this_oldid,const,const | 1 | |
| 1 | PRIMARY | recentchanges_actor | eq_ref | PRIMARY,actor_user | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | Using where |
| 1 | PRIMARY | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_comment_id | 1 | |
| 2 | DEPENDENT SUBQUERY | change_tag | ref | ct_rc_tag_id,ct_tag_id_id | ct_rc_tag_id | 5 | enwiki.recentchanges.rc_id | 1 | Using index |
| 2 | DEPENDENT SUBQUERY | change_tag_def | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.change_tag.ct_tag_id | 1 | |
+------+--------------------+---------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+------------------------------------------------+---------+-------------+
9 rows in set (0.001 sec)
mysql:root@localhost [enwiki]>
```
If forcing 10.6 to use `rc_timestamp` the query goes as fast as the one in 10.4:
```
SELECT rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,page_latest,(SELECT GROUP_CONCAT(ctd_name SEPARATOR ',') FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id)) WHERE ct_rc_id=rc_id ) AS `ts_tags`,fp_stable,fp_pending_since,ores_damaging_cls.oresc_probability AS `ores_damaging_score`,ores_goodfaith_cls.oresc_probability AS `ores_goodfaith_score` FROM `recentchanges` USE INDEX (rc_timestamp) JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) LEFT JOIN `flaggedpages` ON ((fp_page_id = rc_cur_id)) LEFT JOIN `ores_classification` `ores_damaging_cls` ON (ores_damaging_cls.oresc_model = 59 AND (ores_damaging_cls.oresc_rev=rc_this_oldid) AND ores_damaging_cls.oresc_class = 1) LEFT JOIN `ores_classification` `ores_goodfaith_cls` ON (ores_goodfaith_cls.oresc_model = 60 AND (ores_goodfaith_cls.oresc_rev=rc_this_oldid) AND ores_goodfaith_cls.oresc_class = 1) WHERE (actor_user IS NOT NULL) AND rc_bot = 0 AND (rc_minor = 0) AND ((rc_this_oldid = page_latest) OR rc_type = 3) AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace = 0) AND (rc_timestamp >= '20220316104610') AND rc_new IN (0,1) ORDER BY rc_timestamp DESC LIMIT 500;
<snip>
500 rows in set (0.048 sec)
```
Second query:
Upstream bug: https://jira.mariadb.org/browse/MDEV-28476
This query is also different on 10.6 than on 10.4 (which performs better)
```
root@db1135.eqiad.wmnet[enwiki]> select @@version
-> ;
+---------------------+
| @@version |
+---------------------+
| 10.4.21-MariaDB-log |
+---------------------+
1 row in set (0.001 sec)
root@db1135.eqiad.wmnet[enwiki]> explain SELECT actor_name,actor_user,rc_actor,rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid` FROM `recentchanges` JOIN `actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) WHERE rc_namespace = 0 AND rc_type IN (0,1,3,6) ORDER BY rc_timestamp DESC,rc_id DESC LIMIT 501 ;
+------+-------------+--------------------+--------+------------------------------------------------------------------------------------+--------------+---------+------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------------+--------+------------------------------------------------------------------------------------+--------------+---------+------------------------------------+------+-------------+
| 1 | SIMPLE | recentchanges | index | rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp | rc_timestamp | 14 | NULL | 1002 | Using where |
| 1 | SIMPLE | actor | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | |
| 1 | SIMPLE | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_comment_id | 1 | |
+------+-------------+--------------------+--------+------------------------------------------------------------------------------------+--------------+---------+------------------------------------+------+-------------+
3 rows in set (0.001 sec)
```
10.6:
```
root@db1132.eqiad.wmnet[enwiki]> select @@version; explain SELECT actor_name,actor_user,rc_actor,rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid` FROM `recentchanges` JOIN `actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) WHERE rc_namespace = 0 AND rc_type IN (0,1,3,6) ORDER BY rc_timestamp DESC,rc_id DESC LIMIT 501 ;
+--------------------+
| @@version |
+--------------------+
| 10.6.7-MariaDB-log |
+--------------------+
1 row in set (0.000 sec)
+------+-------------+--------------------+--------+------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------------+---------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------------+--------+------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------------+---------+----------------------------------------------------+
| 1 | SIMPLE | recentchanges | ref | rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp | rc_name_type_patrolled_timestamp | 4 | const | 4610595 | Using index condition; Using where; Using filesort |
| 1 | SIMPLE | actor | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | |
| 1 | SIMPLE | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_comment_id | 1 | |
+------+-------------+--------------------+--------+------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------------+---------+----------------------------------------------------+
3 rows in set (0.001 sec)
```
10.6 query with the `USE INDEX`
```
root@db1132.eqiad.wmnet[enwiki]> select @@version; explain SELECT actor_name,actor_user,rc_actor,rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid` FROM `recentchanges` USE INDEX (rc_timestamp) JOIN `actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) WHERE rc_namespace = 0 AND rc_type IN (0,1,3,6) ORDER BY rc_timestamp DESC,rc_id DESC LIMIT 501 ;
+--------------------+
| @@version |
+--------------------+
| 10.6.7-MariaDB-log |
+--------------------+
1 row in set (0.000 sec)
+------+-------------+--------------------+--------+---------------+--------------+---------+------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------------+--------+---------------+--------------+---------+------------------------------------+------+-------------+
| 1 | SIMPLE | recentchanges | index | NULL | rc_timestamp | 14 | NULL | 1298 | Using where |
| 1 | SIMPLE | actor | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | |
| 1 | SIMPLE | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_comment_id | 1 | |
+------+-------------+--------------------+--------+---------------+--------------+---------+------------------------------------+------+-------------+
3 rows in set (0.001 sec)
```
Third query:
Upstream bug: Not needed for now, fixed by refreshing table stats.
This query is also different on 10.6 than on 10.4 (which performs better 0.07 vs more than 60 seconds)
```
root@db1127.eqiad.wmnet[metawiki]> select @@version; explain SELECT rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,rc_title,rc_namespace,page_latest,(SELECT GROUP_CONCAT(ctd_name SEPARATOR ',') FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id)) WHERE ct_rc_id=rc_id ) AS `ts_tags` FROM `recentchanges` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) WHERE rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace NOT IN (1198,1199,866,867)) AND (rc_timestamp >= '20220505123521') AND rc_new IN (0,1) ORDER BY rc_timestamp DESC LIMIT 50;
+--------------------+
| @@version |
+--------------------+
| 10.6.7-MariaDB-log |
+--------------------+
1 row in set (0.000 sec)
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------+----------+--------------->
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra >
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------+----------+--------------->
| 1 | PRIMARY | recentchanges_actor | ALL | PRIMARY | NULL | NULL | NULL | 21528923 | Using temporar>
| 1 | PRIMARY | recentchanges | ref | rc_timestamp,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_new_name_timestamp | rc_actor | 8 | metawiki.recentchanges_actor.actor_id | 19 | Using index co>
| 1 | PRIMARY | page | eq_ref | PRIMARY | PRIMARY | 4 | metawiki.recentchanges.rc_cur_id | 1 | >
| 1 | PRIMARY | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | metawiki.recentchanges.rc_comment_id | 1 | >
| 2 | DEPENDENT SUBQUERY | change_tag | ref | ct_rc_tag_id,ct_tag_id_id | ct_rc_tag_id | 5 | metawiki.recentchanges.rc_id | 1 | Using index >
| 2 | DEPENDENT SUBQUERY | change_tag_def | eq_ref | PRIMARY | PRIMARY | 4 | metawiki.change_tag.ct_tag_id | 1 | >
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------+----------+--------------->
6 rows in set (0.001 sec)
```
10.4:
```
root@db1136.eqiad.wmnet[metawiki]> select @@version; explain SELECT rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,rc_title,rc_namespace,page_latest,(SELECT GROUP_CONCAT(ctd_name SEPARATOR ',') FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id)) WHERE ct_rc_id=rc_id ) AS `ts_tags` FROM `recentchanges` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) WHERE rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace NOT IN (1198,1199,866,867)) AND (rc_timestamp >= '20220505123521') AND rc_new IN (0,1) ORDER BY rc_timestamp DESC LIMIT 50;
+---------------------+
| @@version |
+---------------------+
| 10.4.22-MariaDB-log |
+---------------------+
1 row in set (0.000 sec)
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+--------------------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+--------------------------------------+-------+-------------+
| 1 | PRIMARY | recentchanges | range | rc_timestamp,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_new_name_timestamp | rc_timestamp | 14 | NULL | 97016 | Using where |
| 1 | PRIMARY | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | metawiki.recentchanges.rc_comment_id | 1 | |
| 1 | PRIMARY | page | eq_ref | PRIMARY | PRIMARY | 4 | metawiki.recentchanges.rc_cur_id | 1 | |
| 1 | PRIMARY | recentchanges_actor | eq_ref | PRIMARY | PRIMARY | 8 | metawiki.recentchanges.rc_actor | 1 | |
| 2 | DEPENDENT SUBQUERY | change_tag | ref | ct_rc_tag_id,ct_tag_id_id | ct_rc_tag_id | 5 | metawiki.recentchanges.rc_id | 1 | Using index |
| 2 | DEPENDENT SUBQUERY | change_tag_def | eq_ref | PRIMARY | PRIMARY | 4 | metawiki.change_tag.ct_tag_id | 1 | |
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+--------------------------------------+-------+-------------+
6 rows in set (0.004 sec)
```
Forcing the index on 10.6 the query goes super fast (0.07):
```
root@db1127.eqiad.wmnet[metawiki]> select @@version; explain SELECT rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,rc_title,rc_namespace,page_latest,(SELECT GROUP_CONCAT(ctd_name SEPARATOR ',') FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id)) WHERE ct_rc_id=rc_id ) AS `ts_tags` FROM `recentchanges` USE INDEX (rc_timestamp) JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) WHERE rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace NOT IN (1198,1199,866,867)) AND (rc_timestamp >= '20220505123521') AND rc_new IN (0,1) ORDER BY rc_timestamp DESC LIMIT 50;
+--------------------+
| @@version |
+--------------------+
| 10.6.7-MariaDB-log |
+--------------------+
1 row in set (0.000 sec)
+------+--------------------+---------------------+--------+---------------------------+--------------+---------+--------------------------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+---------------------+--------+---------------------------+--------------+---------+--------------------------------------+--------+-------------+
| 1 | PRIMARY | recentchanges | range | rc_timestamp | rc_timestamp | 14 | NULL | 103132 | Using where |
| 1 | PRIMARY | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | metawiki.recentchanges.rc_comment_id | 1 | |
| 1 | PRIMARY | page | eq_ref | PRIMARY | PRIMARY | 4 | metawiki.recentchanges.rc_cur_id | 1 | |
| 1 | PRIMARY | recentchanges_actor | eq_ref | PRIMARY | PRIMARY | 8 | metawiki.recentchanges.rc_actor | 1 | |
| 2 | DEPENDENT SUBQUERY | change_tag | ref | ct_rc_tag_id,ct_tag_id_id | ct_rc_tag_id | 5 | metawiki.recentchanges.rc_id | 1 | Using index |
| 2 | DEPENDENT SUBQUERY | change_tag_def | eq_ref | PRIMARY | PRIMARY | 4 | metawiki.change_tag.ct_tag_id | 1 | |
+------+--------------------+---------------------+--------+---------------------------+--------------+---------+--------------------------------------+--------+-------------+
6 rows in set (0.001 sec)
```