| SELECT col1 FROM test WHERE col1 = 50 AND id > 666666 ORDER BY id ASC LIMIT 1000 | { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `test`.`col1` AS `col1` from `test` where ((`test`.`col1` = 50) and (`test`.`id` > 666666)) order by `test`.`id` limit 1000" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`test`.`col1` = 50) and (`test`.`id` > 666666))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`test`.`id` > 666666) and multiple equal(50, `test`.`col1`))" }, { "transformation": "constant_propagation", "resulting_condition": "((`test`.`id` > 666666) and multiple equal(50, `test`.`col1`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`test`.`id` > 666666) and multiple equal(50, `test`.`col1`))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`test`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`test`", "field": "col1", "equals": "50", "null_rejecting": false }, { "table": "`test`", "field": "col1", "equals": "50", "null_rejecting": false }, { "table": "`test`", "field": "col1", "equals": "50", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "`test`", "range_analysis": { "table_scan": { "rows": 2000000, "cost": 201277 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "id" ] }, { "index": "index_test_on_col2", "usable": false, "cause": "not_applicable" }, { "index": "index_test_on_created_at", "usable": false, "cause": "not_applicable" }, { "index": "index_test_on_col2_col1", "usable": true, "key_parts": [ "col2", "col1", "id" ] }, { "index": "test_extended_index", "usable": true, "key_parts": [ "col1", "id" ] }, { "index": "index_test_on_col1", "usable": true, "key_parts": [ "col1", "id" ] }, { "index": "index_test_on_col1_col2", "usable": true, "key_parts": [ "col1", "col2", "id" ] } ], "best_covering_index_scan": { "index": "index_test_on_col1", "cost": 201952, "chosen": false, "cause": "cost" }, "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "index_test_on_col2_col1", "tree_travel_cost": 1.05, "num_groups": 2000001, "rows": 2000000, "cost": 4.5e6 }, { "index": "test_extended_index", "usable": false, "cause": "prefix_not_const_equality" }, { "index": "index_test_on_col1", "usable": false, "cause": "prefix_not_const_equality" }, { "index": "index_test_on_col1_col2", "tree_travel_cost": 1.05, "num_groups": 77573, "rows": 77572, "cost": 174691 } ] }, "best_skip_scan_summary": { "type": "skip_scan", "index": "index_test_on_col1_col2", "key_parts_used_for_access": [ "col1", "col2", "id" ], "prefix ranges": [ "50 <= col1 <= 50" ], "range": [ "666666 < id" ], "chosen": true }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "666666 < id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1000000, "cost": 100292, "chosen": true }, { "index": "index_test_on_col2_col1", "chosen": false, "cause": "no_valid_range_for_this_index" }, { "index": "test_extended_index", "ranges": [ "50 <= col1 <= 50 AND 666666 < id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 51126, "cost": 5187.5, "chosen": true }, { "index": "index_test_on_col1", "ranges": [ "50 <= col1 <= 50 AND 666666 < id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 51126, "cost": 5163.5, "chosen": true }, { "index": "index_test_on_col1_col2", "ranges": [ "50 <= col1 <= 50" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 77572, "cost": 7909.4, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "intersecting_indexes": [ { "index": "index_test_on_col1", "index_scan_cost": 50.878, "cumulated_index_scan_cost": 50.878, "disk_sweep_cost": 0, "cumulated_total_cost": 50.878, "usable": true, "matching_rows_now": 51126, "isect_covering_with_this_index": true, "chosen": true } ], "clustered_pk": { "clustered_pk_added_to_intersect": false, "cause": "roworder_is_covering" }, "chosen": false, "cause": "too_few_indexes_to_merge" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "index_test_on_col1", "rows": 51126, "ranges": [ "50 <= col1 <= 50 AND 666666 < id" ] }, "rows_for_plan": 51126, "cost_for_plan": 5163.5, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`test`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "test_extended_index", "chosen": false, "cause": "range_uses_more_keyparts" }, { "access_type": "ref", "index": "index_test_on_col1", "chosen": false, "cause": "range_uses_more_keyparts" }, { "access_type": "ref", "index": "index_test_on_col1_col2", "rows": 77572, "cost": 7909.4, "chosen": true }, { "rows_to_scan": 51126, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "range", "range_details": { "used_index": "index_test_on_col1" }, "resulting_rows": 51126, "cost": 10276, "chosen": false } ] }, "condition_filtering_pct": 50, "rows_for_plan": 38786, "cost_for_plan": 7909.4, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`test`.`col1` = 50) and (`test`.`id` > 666666))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`test`", "attached": "((`test`.`col1` = 50) and (`test`.`id` > 666666))" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`test`.`id`", "items": [ { "item": "`test`.`id`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`test`.`id`" } } }, { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "steps": [ ], "index_order_summary": { "table": "`test`", "index_provides_order": false, "order_direction": "undefined", "index": "index_test_on_col1_col2", "plan_changed": false } } }, { "finalizing_table_conditions": [ { "table": "`test`", "original_table_condition": "((`test`.`col1` = 50) and (`test`.`id` > 666666))", "final_table_condition ": "(`test`.`id` > 666666)" } ] }, { "refine_plan": [ { "table": "`test`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table_in_plan_at_position": 0 } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table_in_plan_at_position": 0, "filesort_information": [ { "direction": "asc", "table": "`test`", "field": "id" } ], "filesort_priority_queue_optimization": { "limit": 1000, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 262144, "key_size": 4, "row_size": 12, "max_rows_per_buffer": 1001, "num_rows_estimate": 4368491, "num_rows_found": 26610, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": 20020, "sort_algorithm": "std::stable_sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] }