I have found this query on 10.4 on enwiki:
SELECT /* ApiQueryRecentChanges::run */ rc_id, rc_timestamp, rc_namespace, rc_title, rc_cur_id, rc_type, rc_deleted, actor_rc_user.actor_user AS `rc_user`, actor_rc_user.actor_name AS `rc_user_text`, rc_actor FROM `recentchanges` JOIN `actor` `actor_rc_user` ON ((actor_rc_user.actor_id = rc_actor)) WHERE (rc_timestamp>='20200206000000') AND (rc_timestamp<='20200220190025') AND rc_namespace IN (0, 10, 14, 118) AND rc_type = 1 ORDER BY rc_timestamp ASC, rc_id ASC LIMIT 1001;
It takes a totally different plan from what 10.1 does:
10.4:
root@db1107.eqiad.wmnet[enwiki]> explain SELECT /* ApiQueryRecentChanges::run */ rc_id, rc_timestamp, rc_namespace, rc_title, rc_cur_id, rc_type, rc_deleted, actor_rc_user.actor_user AS `rc_user`, actor_rc_user.actor_name AS `rc_user_text`, rc_actor FROM `recentchanges` JOIN `actor` `actor_rc_user` ON ((actor_rc_user.actor_id = rc_actor)) WHERE (rc_timestamp>='20200206000000') AND (rc_timestamp<='20200220190025') AND rc_namespace IN (0, 10, 14, 118) AND rc_type = 1 ORDER BY rc_timestamp ASC, rc_id ASC LIMIT 1001 ; +------+-------------+---------------+------+-------------------------------------------------------------------------------------------------------+----------+---------+-------------------------------+----------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+---------------+------+-------------------------------------------------------------------------------------------------------+----------+---------+-------------------------------+----------+------------------------------------+ | 1 | SIMPLE | actor_rc_user | ALL | PRIMARY | NULL | NULL | NULL | 74948417 | Using temporary; Using filesort | | 1 | SIMPLE | recentchanges | ref | rc_timestamp,tmp_3,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp | rc_actor | 8 | enwiki.actor_rc_user.actor_id | 8 | Using index condition; Using where | +------+-------------+---------------+------+-------------------------------------------------------------------------------------------------------+----------+---------+-------------------------------+----------+------------------------------------+ 2 rows in set (0.01 sec)
10.1:
root@db1106.eqiad.wmnet[enwiki]> explain SELECT /* ApiQueryRecentChanges::run */ rc_id, rc_timestamp, rc_namespace, rc_title, rc_cur_id, rc_type, rc_deleted, actor_rc_user.actor_user AS `rc_user`, actor_rc_user.actor_name AS `rc_user_text`, rc_actor FROM `recentchanges` JOIN `actor` `actor_rc_user` ON ((actor_rc_user.actor_id = rc_actor)) WHERE (rc_timestamp>='20200206000000') AND (rc_timestamp<='20200220190025') AND rc_namespace IN (0, 10, 14, 118) AND rc_type = 1 ORDER BY rc_timestamp ASC, rc_id ASC LIMIT 1001 ; +------+-------------+---------------+--------+-------------------------------------------------------------------------------------------------+--------------+---------+-------------------------------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+---------------+--------+-------------------------------------------------------------------------------------------------+--------------+---------+-------------------------------+---------+-------------+ | 1 | SIMPLE | recentchanges | range | rc_timestamp,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp | rc_timestamp | 16 | NULL | 3896296 | Using where | | 1 | SIMPLE | actor_rc_user | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | | +------+-------------+---------------+--------+-------------------------------------------------------------------------------------------------+--------------+---------+-------------------------------+---------+-------------+ 2 rows in set (0.00 sec)
Optimizer trace:
root@db1107.eqiad.wmnet[enwiki]> select * from information_schema.optimizer_trace\G *************************** 1. row *************************** QUERY: SELECT rc_id, rc_timestamp, rc_namespace, rc_title, rc_cur_id, rc_type, rc_deleted, actor_rc_user.actor_user AS `rc_user`, actor_rc_user.actor_name AS `rc_user_text`, rc_actor FROM `recentchanges` JOIN `actor` `actor_rc_user` ON ((actor_rc_user.actor_id = rc_actor)) WHERE (rc_timestamp>='20200206000000') AND (rc_timestamp<='20200220190025') AND rc_namespace IN (0, 10, 14, 118) AND rc_type = 1 ORDER BY rc_timestamp ASC, rc_id ASC LIMIT 1001 TRACE: { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select recentchanges.rc_id AS rc_id,recentchanges.rc_timestamp AS rc_timestamp,recentchanges.rc_namespace AS rc_namespace,recentchanges.rc_title AS rc_title,recentchanges.rc_cur_id AS rc_cur_id,recentchanges.rc_type AS rc_type,recentchanges.rc_deleted AS rc_deleted,actor_rc_user.actor_user AS rc_user,actor_rc_user.actor_name AS rc_user_text,recentchanges.rc_actor AS rc_actor from (recentchanges join actor actor_rc_user on(actor_rc_user.actor_id = recentchanges.rc_actor)) where recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118) and recentchanges.rc_type = 1 order by recentchanges.rc_timestamp,recentchanges.rc_id limit 1001" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118) and recentchanges.rc_type = 1 and actor_rc_user.actor_id = recentchanges.rc_actor", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118) and multiple equal(1, recentchanges.rc_type) and multiple equal(actor_rc_user.actor_id, recentchanges.rc_actor)" }, { "transformation": "constant_propagation", "resulting_condition": "recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118) and multiple equal(1, recentchanges.rc_type) and multiple equal(actor_rc_user.actor_id, recentchanges.rc_actor)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118) and multiple equal(1, recentchanges.rc_type) and multiple equal(actor_rc_user.actor_id, recentchanges.rc_actor)" } ] } }, { "table_dependencies": [ { "table": "recentchanges", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "actor_rc_user", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [ { "table": "recentchanges", "field": "rc_actor", "equals": "actor_rc_user.actor_id", "null_rejecting": false }, { "table": "recentchanges", "field": "rc_actor", "equals": "actor_rc_user.actor_id", "null_rejecting": false }, { "table": "actor_rc_user", "field": "actor_id", "equals": "recentchanges.rc_actor", "null_rejecting": false }, { "table": "actor_rc_user", "field": "actor_id", "equals": "recentchanges.rc_actor", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "recentchanges", "range_analysis": { "table_scan": { "rows": 7996261, "cost": 1.74e6 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "rc_timestamp", "usable": true, "key_parts": ["rc_timestamp", "rc_id"] }, { "index": "rc_cur_id", "usable": false, "cause": "not applicable" }, { "index": "new_name_timestamp", "usable": false, "cause": "not applicable" }, { "index": "rc_ip", "usable": false, "cause": "not applicable" }, { "index": "tmp_2", "usable": false, "cause": "not applicable" }, { "index": "tmp_3", "usable": true, "key_parts": ["rc_namespace", "rc_timestamp", "rc_id"] }, { "index": "rc_name_type_patrolled_timestamp", "usable": true, "key_parts": [ "rc_namespace", "rc_type", "rc_patrolled", "rc_timestamp", "rc_id" ] }, { "index": "rc_ns_actor", "usable": true, "key_parts": ["rc_namespace", "rc_actor", "rc_id"] }, { "index": "rc_actor", "usable": false, "cause": "not applicable" }, { "index": "rc_namespace_title_timestamp", "usable": true, "key_parts": [ "rc_namespace", "rc_title", "rc_timestamp", "rc_id" ] }, { "index": "rc_this_oldid", "usable": false, "cause": "not applicable" } ], "setup_range_conditions": [], "group_index_range": { "chosen": false, "cause": "not single_table" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "rc_timestamp", "ranges": [ "(20200206000000) <= (rc_timestamp) <= (20200220190025)" ], "rowid_ordered": false, "using_mrr": true, "index_only": false, "rows": 3998130, "cost": 4.34e6, "chosen": false, "cause": "cost" }, { "index": "tmp_3", "ranges": [ "(0,020200206000000) <= (rc_namespace,rc_timestamp) <= (0,020200220190025)", "(10,1020200206000000) <= (rc_namespace,rc_timestamp) <= (10,1020200220190025)", "(14,1420200206000000) <= (rc_namespace,rc_timestamp) <= (14,1420200220190025)", "(118,11820200206000000) <= (rc_namespace,rc_timestamp) <= (118,11820200220190025)" ], "rowid_ordered": false, "using_mrr": true, "index_only": false, "rows": 6662832, "cost": 7.06e6, "chosen": false, "cause": "cost" }, { "index": "rc_name_type_patrolled_timestamp", "ranges": [ "(0,1) <= (rc_namespace,rc_type) <= (0,1)", "(10,1) <= (rc_namespace,rc_type) <= (10,1)", "(14,1) <= (rc_namespace,rc_type) <= (14,1)", "(118,1) <= (rc_namespace,rc_type) <= (118,1)" ], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 139882, "cost": 175345, "chosen": true }, { "index": "rc_ns_actor", "ranges": [ "(0) <= (rc_namespace) <= (0)", "(10) <= (rc_namespace) <= (10)", "(14) <= (rc_namespace) <= (14)", "(118) <= (rc_namespace) <= (118)" ], "rowid_ordered": false, "using_mrr": true, "index_only": false, "rows": 7996261, "cost": 8.41e6, "chosen": false, "cause": "cost" }, { "index": "rc_namespace_title_timestamp", "ranges": [ "(0) <= (rc_namespace) <= (0)", "(10) <= (rc_namespace) <= (10)", "(14) <= (rc_namespace) <= (14)", "(118) <= (rc_namespace) <= (118)" ], "rowid_ordered": false, "using_mrr": true, "index_only": false, "rows": 7996261, "cost": 8.69e6, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "rc_name_type_patrolled_timestamp", "rows": 139882, "ranges": [ "(0,1) <= (rc_namespace,rc_type) <= (0,1)", "(10,1) <= (rc_namespace,rc_type) <= (10,1)", "(14,1) <= (rc_namespace,rc_type) <= (14,1)", "(118,1) <= (rc_namespace,rc_type) <= (118,1)" ] }, "rows_for_plan": 139882, "cost_for_plan": 175345, "chosen": true } } }, { "selectivity_for_indexes": [ { "index_name": "tmp_3", "selectivity_from_index": 0.8332 } ], "selectivity_for_columns": [], "cond_selectivity": 0.8332 }, { "table": "actor_rc_user", "table_scan": { "rows": 74948426, "cost": 227008 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "table": "recentchanges", "best_access_path": { "considered_access_paths": [ { "access_type": "range", "resulting_rows": 6.66e6, "cost": 2e308, "chosen": true } ], "chosen_access_method": { "type": "range", "records": 6.66e6, "cost": 2e308, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 6.66e6, "cost_for_plan": 2e308, "rest_of_plan": [ { "plan_prefix": ["recentchanges"], "table": "actor_rc_user", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 6.66e6, "chosen": true }, { "access_type": "scan", "resulting_rows": 7.49e7, "cost": 1.68e9, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 6.66e6, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 6.66e6, "cost_for_plan": 2e308, "estimated_join_cardinality": 6.66e6 } ] }, { "plan_prefix": [], "table": "actor_rc_user", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 7.49e7, "cost": 227008, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 7.49e7, "cost": 227008, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 7.49e7, "cost_for_plan": 1.52e7, "rest_of_plan": [ { "plan_prefix": ["actor_rc_user"], "table": "recentchanges", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "rc_actor", "rows": 8, "cost": 6.75e8, "chosen": true }, { "access_type": "range", "resulting_rows": 6.66e6, "cost": 2e308, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 8, "cost": 6.75e8, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 6e8, "cost_for_plan": 8.1e8, "selectivity": 0.8332, "estimated_join_cardinality": 5e8 } ] } ] }, { "best_join_order": ["actor_rc_user", "recentchanges"] }, { "attaching_conditions_to_tables": { "original_condition": "recentchanges.rc_type = 1 and recentchanges.rc_actor = actor_rc_user.actor_id and recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118)", "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "actor_rc_user", "attached": null }, { "table": "recentchanges", "attached": "recentchanges.rc_type = 1 and recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118)" } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0
Forcing the indexes 10.1 chooses, makes the query a lot faster and the explain makes more sense as it scans less rows:
root@db1107.eqiad.wmnet[enwiki]> explain SELECT /* ApiQueryRecentChanges::run */ rc_id, rc_timestamp, rc_namespace, rc_title, rc_cur_id, rc_type, rc_deleted, actor_rc_user.actor_user AS `rc_user`, actor_rc_user.actor_name AS `rc_user_text`, rc_actor FROM `recentchanges` USE INDEX (rc_timestamp )JOIN `actor` `actor_rc_user` USE INDEX (PRIMARY) ON ((actor_rc_user.actor_id = rc_actor)) WHERE (rc_timestamp>='20200206000000') AND (rc_timestamp<='20200220190025') AND rc_namespace IN (0, 10, 14, 118) AND rc_type = 1 ORDER BY rc_timestamp ASC, rc_id ASC LIMIT 1001; +------+-------------+---------------+--------+---------------+--------------+---------+-------------------------------+---------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+---------------+--------+---------------+--------------+---------+-------------------------------+---------+------------------------------------+ | 1 | SIMPLE | recentchanges | range | rc_timestamp | rc_timestamp | 16 | NULL | 3998351 | Using index condition; Using where | | 1 | SIMPLE | actor_rc_user | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | | +------+-------------+---------------+--------+---------------+--------------+---------+-------------------------------+---------+------------------------------------+ 2 rows in set (0.00 sec)
The trace for this query is much better and the cost is smaller and the execution plan is faster:
root@db1107.eqiad.wmnet[enwiki]> select * from information_schema.optimizer_trace\G *************************** 1. row *************************** QUERY: SELECT rc_id, rc_timestamp, rc_namespace, rc_title, rc_cur_id, rc_type, rc_deleted, actor_rc_user.actor_user AS `rc_user`, actor_rc_user.actor_name AS `rc_user_text`, rc_actor FROM `recentchanges` USE INDEX (rc_timestamp )JOIN `actor` `actor_rc_user` USE INDEX (PRIMARY) ON ((actor_rc_user.actor_id = rc_actor)) WHERE (rc_timestamp>='20200206000000') AND (rc_timestamp<='20200220190025') AND rc_namespace IN (0, 10, 14, 118) AND rc_type = 1 ORDER BY rc_timestamp ASC, rc_id ASC LIMIT 1001 TRACE: { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select recentchanges.rc_id AS rc_id,recentchanges.rc_timestamp AS rc_timestamp,recentchanges.rc_namespace AS rc_namespace,recentchanges.rc_title AS rc_title,recentchanges.rc_cur_id AS rc_cur_id,recentchanges.rc_type AS rc_type,recentchanges.rc_deleted AS rc_deleted,actor_rc_user.actor_user AS rc_user,actor_rc_user.actor_name AS rc_user_text,recentchanges.rc_actor AS rc_actor from (recentchanges USE INDEX (rc_timestamp) join actor actor_rc_user USE INDEX (PRIMARY) on(actor_rc_user.actor_id = recentchanges.rc_actor)) where recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118) and recentchanges.rc_type = 1 order by recentchanges.rc_timestamp,recentchanges.rc_id limit 1001" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118) and recentchanges.rc_type = 1 and actor_rc_user.actor_id = recentchanges.rc_actor", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118) and multiple equal(1, recentchanges.rc_type) and multiple equal(actor_rc_user.actor_id, recentchanges.rc_actor)" }, { "transformation": "constant_propagation", "resulting_condition": "recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118) and multiple equal(1, recentchanges.rc_type) and multiple equal(actor_rc_user.actor_id, recentchanges.rc_actor)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118) and multiple equal(1, recentchanges.rc_type) and multiple equal(actor_rc_user.actor_id, recentchanges.rc_actor)" } ] } }, { "table_dependencies": [ { "table": "recentchanges", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "actor_rc_user", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [ { "table": "recentchanges", "field": "rc_actor", "equals": "actor_rc_user.actor_id", "null_rejecting": false }, { "table": "actor_rc_user", "field": "actor_id", "equals": "recentchanges.rc_actor", "null_rejecting": false }, { "table": "actor_rc_user", "field": "actor_id", "equals": "recentchanges.rc_actor", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "recentchanges", "range_analysis": { "table_scan": { "rows": 7996798, "cost": 1.74e6 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "rc_timestamp", "usable": true, "key_parts": ["rc_timestamp", "rc_id"] }, { "index": "rc_cur_id", "usable": false, "cause": "not applicable" }, { "index": "new_name_timestamp", "usable": false, "cause": "not applicable" }, { "index": "rc_ip", "usable": false, "cause": "not applicable" }, { "index": "tmp_2", "usable": false, "cause": "not applicable" }, { "index": "tmp_3", "usable": false, "cause": "not applicable" }, { "index": "rc_name_type_patrolled_timestamp", "usable": false, "cause": "not applicable" }, { "index": "rc_ns_actor", "usable": false, "cause": "not applicable" }, { "index": "rc_actor", "usable": false, "cause": "not applicable" }, { "index": "rc_namespace_title_timestamp", "usable": false, "cause": "not applicable" }, { "index": "rc_this_oldid", "usable": false, "cause": "not applicable" } ], "setup_range_conditions": [], "group_index_range": { "chosen": false, "cause": "not single_table" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "rc_timestamp", "ranges": [ "(20200206000000) <= (rc_timestamp) <= (20200220190025)" ], "rowid_ordered": false, "using_mrr": true, "index_only": false, "rows": 3998399, "cost": 4.34e6, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] } } }, { "selectivity_for_indexes": [ { "index_name": "rc_timestamp", "selectivity_from_index": 0.5 } ], "selectivity_for_columns": [], "cond_selectivity": 0.5 }, { "table": "actor_rc_user", "table_scan": { "rows": 74948426, "cost": 227008 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "table": "recentchanges", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 4e6, "cost": 942209, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 4e6, "cost": 942209, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 4e6, "cost_for_plan": 1.74e6, "rest_of_plan": [ { "plan_prefix": ["recentchanges"], "table": "actor_rc_user", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 4e6, "chosen": true }, { "access_type": "scan", "resulting_rows": 7.49e7, "cost": 1.01e9, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 4e6, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 4e6, "cost_for_plan": 6.54e6, "estimated_join_cardinality": 4e6 } ] }, { "plan_prefix": [], "table": "actor_rc_user", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 7.49e7, "cost": 227008, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 7.49e7, "cost": 227008, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 7.49e7, "cost_for_plan": 1.52e7, "pruned_by_cost": true } ] }, { "best_join_order": ["recentchanges", "actor_rc_user"] }, { "attaching_conditions_to_tables": { "original_condition": "recentchanges.rc_type = 1 and actor_rc_user.actor_id = recentchanges.rc_actor and recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118)", "attached_conditions_computation": [ { "table": "recentchanges", "range_analysis": { "table_scan": { "rows": 7996798, "cost": 9.6e6 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "rc_timestamp", "usable": true, "key_parts": ["rc_timestamp", "rc_id"] }, { "index": "rc_cur_id", "usable": false, "cause": "not applicable" }, { "index": "new_name_timestamp", "usable": false, "cause": "not applicable" }, { "index": "rc_ip", "usable": false, "cause": "not applicable" }, { "index": "tmp_2", "usable": false, "cause": "not applicable" }, { "index": "tmp_3", "usable": false, "cause": "not applicable" }, { "index": "rc_name_type_patrolled_timestamp", "usable": false, "cause": "not applicable" }, { "index": "rc_ns_actor", "usable": false, "cause": "not applicable" }, { "index": "rc_actor", "usable": false, "cause": "not applicable" }, { "index": "rc_namespace_title_timestamp", "usable": false, "cause": "not applicable" }, { "index": "rc_this_oldid", "usable": false, "cause": "not applicable" } ], "setup_range_conditions": [], "group_index_range": { "chosen": false, "cause": "not single_table" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "rc_timestamp", "ranges": [ "(20200206000000) <= (rc_timestamp) <= (20200220190025)" ], "rowid_ordered": false, "using_mrr": true, "index_only": false, "rows": 3998399, "cost": 4.34e6, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "rc_timestamp", "rows": 3998399, "ranges": [ "(20200206000000) <= (rc_timestamp) <= (20200220190025)" ] }, "rows_for_plan": 3998399, "cost_for_plan": 4.34e6, "chosen": true } } } ], "attached_conditions_summary": [ { "table": "recentchanges", "attached": "recentchanges.rc_type = 1 and recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118)" }, { "table": "actor_rc_user", "attached": null } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.00 sec)
It might make sense to report this to MariaDB with the full trace