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)