I have noticed the following slow query on 10.4 (around 12 seconds runtime):
{P10509}
That query doesn't seem slow on 10.1
After optimizing recentchanges to made sure we had the latest stats.
The explain shows as follows:
+------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+---------+-------------+ | 1 | PRIMARY | recentchanges | ref | rc_timestamp,new_name_timestamp,tmp_2,tmp_3,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_this_oldid | tmp_2 | 1 | const | 4274159 | Using where | | 1 | PRIMARY | watchlist | eq_ref | wl_user,namespace_title,wl_user_notificationtimestamp | wl_user | 265 | const,enwiki.recentchanges.rc_namespace,enwiki.recentchanges.rc_title | 1 | | | 1 | PRIMARY | actor_rc_user | eq_ref | PRIMARY,actor_user | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | | | 1 | PRIMARY | user | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.actor_rc_user.actor_user | 1 | 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 | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_comment_id | 1 | | | 2 | DEPENDENT SUBQUERY | change_tag | ref | change_tag_rc_tag_id,change_tag_tag_id_id | change_tag_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 | | +------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+---------+-------------+ 11 rows in set (0.00 sec)
It is picking tmp_2 as an index, while 10.1 picks tmp_3, which is a lot better:
+------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+------+----------------------------------------------------+ | 1 | PRIMARY | recentchanges | range | rc_timestamp,new_name_timestamp,tmp_2,tmp_3,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_this_oldid | tmp_3 | 20 | NULL | 958 | Using index condition; Using where; Using filesort | | 1 | PRIMARY | watchlist | eq_ref | wl_user,namespace_title,wl_user_notificationtimestamp | wl_user | 265 | const,enwiki.recentchanges.rc_namespace,enwiki.recentchanges.rc_title | 1 | | | 1 | PRIMARY | actor_rc_user | eq_ref | PRIMARY,actor_user | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | | | 1 | PRIMARY | user | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.actor_rc_user.actor_user | 1 | 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 | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_comment_id | 1 | | | 2 | DEPENDENT SUBQUERY | change_tag | ref | change_tag_rc_tag_id,change_tag_tag_id_id | change_tag_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 | | +------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+------+----------------------------------------------------+
The optimize trace actually shows that tmp_3 index is a better election, but it chooses tmp_2 anyways:
{
"index": "tmp_2",
"ranges": ["(0,020200224144839) <= (rc_bot,rc_timestamp)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 405574,
"cost": 508110,
"chosen": false,
"cause": "cost"
},
{
"index": "tmp_3",
"ranges": [
"(6,620200224144839) <= (rc_namespace,rc_timestamp)",
"(7,720200224144839) <= (rc_namespace,rc_timestamp)"
],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 957,
"cost": 1201,
"chosen": true
"considered_execution_plans": [
{
"plan_prefix": [],
"table": "recentchanges",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "tmp_2",
"rows": 4.27e6,
"cost": 440644,
"chosen": true
},Full optimizer trace at P10509#60991
root@db1107.eqiad.wmnet[(none)]> show explain for 324762816; +------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+---------+-------------+ | 1 | PRIMARY | recentchanges | ref | rc_timestamp,new_name_timestamp,tmp_2,tmp_3,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_this_oldid | tmp_2 | 1 | const | 4262230 | Using where | | 1 | PRIMARY | watchlist | eq_ref | wl_user,namespace_title,wl_user_notificationtimestamp | wl_user | 265 | const,enwiki.recentchanges.rc_namespace,enwiki.recentchanges.rc_title | 1 | | | 1 | PRIMARY | actor_rc_user | eq_ref | PRIMARY,actor_user | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | | | 1 | PRIMARY | user | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.actor_rc_user.actor_user | 1 | 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 | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_comment_id | 1 | | | 2 | DEPENDENT SUBQUERY | change_tag | ref | change_tag_rc_tag_id,change_tag_tag_id_id | change_tag_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 | | +------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+---------+-------------+ 11 rows in set, 1 warning (0.01 sec)
Forcing index tmp_3 on 10.4 shows the same result than on 10.1 and a lot faster query 12 seconds vs 0.02 query runtime even though it does failsort
Explain
+------+--------------------+--------------------+--------+-------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+--------------------+--------+-------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+------+----------------------------------------------------+ | 1 | PRIMARY | recentchanges | range | tmp_3 | tmp_3 | 20 | NULL | 962 | Using index condition; Using where; Using filesort | | 1 | PRIMARY | watchlist | eq_ref | wl_user,namespace_title,wl_user_notificationtimestamp | wl_user | 265 | const,enwiki.recentchanges.rc_namespace,enwiki.recentchanges.rc_title | 1 | | | 1 | PRIMARY | actor_rc_user | eq_ref | PRIMARY,actor_user | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | | | 1 | PRIMARY | user | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.actor_rc_user.actor_user | 1 | 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 | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_comment_id | 1 | | | 2 | DEPENDENT SUBQUERY | change_tag | ref | change_tag_rc_tag_id,change_tag_tag_id_id | change_tag_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 | | +------+--------------------+--------------------+--------+-------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+------+----------------------------------------------------+ 11 rows in set (0.01 sec)