{ "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select 1 from (((`depot` join `location` on((`location`.`id` = `depot`.`location_id`))) join `city` on((`city`.`id` = `location`.`city_id`))) join `country` on((`country`.`id` = `city`.`country_id`))) where ((`country`.`id` = `c`.`id`) and (`depot`.`deleted` = 0))" }, { "transformation": { "select#": 2, "from": "EXISTS (SELECT)", "to": "semijoin", "chosen": true } } ] } }, { "expanded_query": "/* select#1 */ select `c`.`id` AS `id` from `country` `c` where exists(/* select#2 */ select 1 from (((`depot` join `location` on((`location`.`id` = `depot`.`location_id`))) join `city` on((`city`.`id` = `location`.`city_id`))) join `country` on((`country`.`id` = `city`.`country_id`))) where ((`country`.`id` = `c`.`id`) and (`depot`.`deleted` = 0)))" }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true, "transformation_to_semi_join": { "subquery_predicate": "exists(/* select#2 */ select 1 from (((`depot` join `location` on((`location`.`id` = `depot`.`location_id`))) join `city` on((`city`.`id` = `location`.`city_id`))) join `country` on((`country`.`id` = `city`.`country_id`))) where ((`country`.`id` = `c`.`id`) and (`depot`.`deleted` = 0)))", "embedded in": "WHERE", "evaluating_constant_semijoin_conditions": [ ], "semi-join condition": "((`depot`.`deleted` = 0) and (`c`.`id` = `country`.`id`))", "decorrelated_predicates": [ { "outer": "`c`.`id`", "inner": "`country`.`id`" } ] } } }, { "transformations_to_nested_joins": { "transformations": [ "semijoin", "JOIN_condition_to_WHERE", "parenthesis_removal" ], "expanded_query": "/* select#1 */ select `c`.`id` AS `id` from `country` `c` semi join (`depot` join `location` join `city` join `country`) where ((`depot`.`deleted` = 0) and (`c`.`id` = `country`.`id`) and (`country`.`id` = `city`.`country_id`) and (`city`.`id` = `location`.`city_id`) and (`location`.`id` = `depot`.`location_id`))" } } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`depot`.`deleted` = 0) and (`c`.`id` = `country`.`id`) and (`country`.`id` = `city`.`country_id`) and (`city`.`id` = `location`.`city_id`) and (`location`.`id` = `depot`.`location_id`))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(multiple equal(0, `depot`.`deleted`) and multiple equal(`c`.`id`, `country`.`id`, `city`.`country_id`) and multiple equal(`city`.`id`, `location`.`city_id`) and multiple equal(`location`.`id`, `depot`.`location_id`))" }, { "transformation": "constant_propagation", "resulting_condition": "(multiple equal(0, `depot`.`deleted`) and multiple equal(`c`.`id`, `country`.`id`, `city`.`country_id`) and multiple equal(`city`.`id`, `location`.`city_id`) and multiple equal(`location`.`id`, `depot`.`location_id`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(multiple equal(0, `depot`.`deleted`) and multiple equal(`c`.`id`, `country`.`id`, `city`.`country_id`) and multiple equal(`city`.`id`, `location`.`city_id`) and multiple equal(`location`.`id`, `depot`.`location_id`))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`country` `c`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] }, { "table": "`depot`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] }, { "table": "`location`", "row_may_be_null": false, "map_bit": 2, "depends_on_map_bits": [ ] }, { "table": "`city`", "row_may_be_null": false, "map_bit": 3, "depends_on_map_bits": [ ] }, { "table": "`country`", "row_may_be_null": false, "map_bit": 4, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`country` `c`", "field": "id", "equals": "`country`.`id`", "null_rejecting": false }, { "table": "`country` `c`", "field": "id", "equals": "`city`.`country_id`", "null_rejecting": false }, { "table": "`depot`", "field": "location_id", "equals": "`location`.`id`", "null_rejecting": false }, { "table": "`location`", "field": "id", "equals": "`depot`.`location_id`", "null_rejecting": false }, { "table": "`location`", "field": "city_id", "equals": "`city`.`id`", "null_rejecting": false }, { "table": "`location`", "field": "id", "equals": "`depot`.`location_id`", "null_rejecting": false }, { "table": "`city`", "field": "id", "equals": "`location`.`city_id`", "null_rejecting": false }, { "table": "`city`", "field": "country_id", "equals": "`c`.`id`", "null_rejecting": false }, { "table": "`city`", "field": "country_id", "equals": "`country`.`id`", "null_rejecting": false }, { "table": "`city`", "field": "id", "equals": "`location`.`city_id`", "null_rejecting": false }, { "table": "`country`", "field": "id", "equals": "`c`.`id`", "null_rejecting": false }, { "table": "`country`", "field": "id", "equals": "`city`.`country_id`", "null_rejecting": false } ] }, { "pulled_out_semijoin_tables": [ { "table": "`country`", "functionally_dependent": true } ] }, { "rows_estimation": [ { "table": "`country` `c`", "table_scan": { "rows": 236, "cost": 0.25 } }, { "table": "`depot`", "table_scan": { "rows": 119232, "cost": 56.25 } }, { "table": "`location`", "table_scan": { "rows": 96688, "cost": 111 } }, { "table": "`city`", "table_scan": { "rows": 15531, "cost": 41.277 } }, { "table": "`country`", "table_scan": { "rows": 236, "cost": 0.25 } } ] }, { "execution_plan_for_potential_materialization": { "steps": [ ] } }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`country` `c`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "rows_to_scan": 236, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "resulting_rows": 236, "cost": 23.85, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 236, "cost_for_plan": 23.85, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`country` `c`" ], "table": "`country`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 82.6, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 236, "cost_for_plan": 106.45, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`country` `c`", "`country`" ], "table": "`city`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "ref", "index": "country_id", "rows": 68.721, "cost": 1873.4, "chosen": true }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "added_to_eq_ref_extension": false }, { "plan_prefix": [ "`country` `c`", "`country`" ], "table": "`city`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "ref", "index": "country_id", "rows": 68.721, "cost": 1873.4, "chosen": true }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 16218, "cost_for_plan": 1979.9, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`country` `c`", "`country`", "`city`" ], "table": "`location`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "ref", "index": "city_id", "rows": 6.1079, "cost": 22415, "chosen": true }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 99059, "cost_for_plan": 24395, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`country` `c`", "`country`", "`city`", "`location`" ], "table": "`depot`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "location_id", "rows": 6.4349, "cost": 223102, "chosen": true }, { "rows_to_scan": 119232, "filtering_effect": [ ], "final_filtering_effect": 0.1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 10, "resulting_rows": 11923, "cost": 1.18e8, "chosen": false } ] }, "condition_filtering_pct": 10, "rows_for_plan": 63743, "cost_for_plan": 247497, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "recalculate_access_paths_and_cost": { "tables": [ ] }, "cost": 247497, "rows": 236, "chosen": true }, { "strategy": "DuplicatesWeedout", "cost": 253896, "rows": 236, "duplicate_tables_left": false, "chosen": false } ], "chosen": true } ] }, { "plan_prefix": [ "`country` `c`", "`country`", "`city`" ], "table": "`depot`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "location_id", "usable": false, "chosen": false }, { "rows_to_scan": 119232, "filtering_effect": [ ], "final_filtering_effect": 0.1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 11923, "cost": 1.94e7, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 1.93e8, "cost_for_plan": 1.94e7, "semijoin_strategy_choice": [ ], "pruned_by_cost": true } ] }, { "plan_prefix": [ "`country` `c`", "`country`" ], "table": "`location`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "ref", "index": "city_id", "usable": false, "chosen": false }, { "rows_to_scan": 96688, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 96688, "cost": 2.28e6, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 2.28e7, "cost_for_plan": 2.28e6, "semijoin_strategy_choice": [ ], "pruned_by_cost": true }, { "plan_prefix": [ "`country` `c`", "`country`" ], "table": "`depot`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "location_id", "usable": false, "chosen": false }, { "rows_to_scan": 119232, "filtering_effect": [ ], "final_filtering_effect": 0.1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 11923, "cost": 292252, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 2.81e6, "cost_for_plan": 292359, "semijoin_strategy_choice": [ ], "pruned_by_cost": true } ] }, { "plan_prefix": [ "`country` `c`" ], "table": "`city`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "ref", "index": "country_id", "rows": 68.721, "cost": 1873.4, "chosen": true }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 16218, "cost_for_plan": 1897.3, "semijoin_strategy_choice": [ ], "pruned_by_heuristic": true }, { "plan_prefix": [ "`country` `c`" ], "table": "`location`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "ref", "index": "city_id", "usable": false, "chosen": false }, { "rows_to_scan": 96688, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 96688, "cost": 2.28e6, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 2.28e7, "cost_for_plan": 2.28e6, "semijoin_strategy_choice": [ ], "pruned_by_cost": true }, { "plan_prefix": [ "`country` `c`" ], "table": "`depot`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "location_id", "usable": false, "chosen": false }, { "rows_to_scan": 119232, "filtering_effect": [ ], "final_filtering_effect": 0.1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 11923, "cost": 292213, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 2.81e6, "cost_for_plan": 292237, "semijoin_strategy_choice": [ ], "pruned_by_cost": true } ] }, { "plan_prefix": [ ], "table": "`country`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "rows_to_scan": 236, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "resulting_rows": 236, "cost": 23.85, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 236, "cost_for_plan": 23.85, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`country`" ], "table": "`country` `c`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 82.6, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 236, "cost_for_plan": 106.45, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`country`", "`country` `c`" ], "table": "`city`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "ref", "index": "country_id", "rows": 68.721, "cost": 1873.4, "chosen": true }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "added_to_eq_ref_extension": false }, { "plan_prefix": [ "`country`", "`country` `c`" ], "table": "`city`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "ref", "index": "country_id", "rows": 68.721, "cost": 1873.4, "chosen": true }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 16218, "cost_for_plan": 1979.9, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`country`", "`country` `c`", "`city`" ], "table": "`location`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "ref", "index": "city_id", "rows": 6.1079, "cost": 22415, "chosen": true }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 99059, "cost_for_plan": 24395, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`country`", "`country` `c`", "`city`", "`location`" ], "table": "`depot`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "location_id", "rows": 6.4349, "cost": 223102, "chosen": true }, { "rows_to_scan": 119232, "filtering_effect": [ ], "final_filtering_effect": 0.1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 10, "resulting_rows": 11923, "cost": 1.18e8, "chosen": false } ] }, "condition_filtering_pct": 10, "rows_for_plan": 63743, "cost_for_plan": 247497, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "recalculate_access_paths_and_cost": { "tables": [ ] }, "cost": 247497, "rows": 236, "chosen": true }, { "strategy": "DuplicatesWeedout", "cost": 253896, "rows": 236, "duplicate_tables_left": false, "chosen": false } ], "pruned_by_cost": true } ] }, { "plan_prefix": [ "`country`", "`country` `c`", "`city`" ], "table": "`depot`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "location_id", "usable": false, "chosen": false }, { "rows_to_scan": 119232, "filtering_effect": [ ], "final_filtering_effect": 0.1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 11923, "cost": 1.94e7, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 1.93e8, "cost_for_plan": 1.94e7, "semijoin_strategy_choice": [ ], "pruned_by_cost": true } ] }, { "plan_prefix": [ "`country`", "`country` `c`" ], "table": "`location`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "ref", "index": "city_id", "usable": false, "chosen": false }, { "rows_to_scan": 96688, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 96688, "cost": 2.28e6, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 2.28e7, "cost_for_plan": 2.28e6, "semijoin_strategy_choice": [ ], "pruned_by_cost": true }, { "plan_prefix": [ "`country`", "`country` `c`" ], "table": "`depot`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "location_id", "usable": false, "chosen": false }, { "rows_to_scan": 119232, "filtering_effect": [ ], "final_filtering_effect": 0.1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 11923, "cost": 292252, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 2.81e6, "cost_for_plan": 292359, "semijoin_strategy_choice": [ ], "pruned_by_cost": true } ] }, { "plan_prefix": [ "`country`" ], "table": "`city`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "ref", "index": "country_id", "rows": 68.721, "cost": 1873.4, "chosen": true }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 16218, "cost_for_plan": 1897.3, "semijoin_strategy_choice": [ ], "pruned_by_heuristic": true }, { "plan_prefix": [ "`country`" ], "table": "`location`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "ref", "index": "city_id", "usable": false, "chosen": false }, { "rows_to_scan": 96688, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 96688, "cost": 2.28e6, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 2.28e7, "cost_for_plan": 2.28e6, "semijoin_strategy_choice": [ ], "pruned_by_cost": true }, { "plan_prefix": [ "`country`" ], "table": "`depot`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "location_id", "usable": false, "chosen": false }, { "rows_to_scan": 119232, "filtering_effect": [ ], "final_filtering_effect": 0.1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 11923, "cost": 292213, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 2.81e6, "cost_for_plan": 292237, "semijoin_strategy_choice": [ ], "pruned_by_cost": true } ] }, { "plan_prefix": [ ], "table": "`city`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "ref", "index": "country_id", "usable": false, "chosen": false }, { "rows_to_scan": 15531, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "resulting_rows": 15531, "cost": 1594.4, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 15531, "cost_for_plan": 1594.4, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`city`" ], "table": "`country` `c`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 5435.9, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 15531, "cost_for_plan": 7030.2, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`city`", "`country` `c`" ], "table": "`country`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 5435.9, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "added_to_eq_ref_extension": true, "condition_filtering_pct": 100, "rows_for_plan": 15531, "cost_for_plan": 12466, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`city`", "`country` `c`", "`country`" ], "table": "`location`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "ref", "index": "city_id", "rows": 6.1079, "cost": 21466, "chosen": true }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "added_to_eq_ref_extension": false }, { "plan_prefix": [ "`city`", "`country` `c`", "`country`" ], "table": "`location`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "ref", "index": "city_id", "rows": 6.1079, "cost": 21466, "chosen": true }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 94862, "cost_for_plan": 33932, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`city`", "`country` `c`", "`country`", "`location`" ], "table": "`depot`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "location_id", "rows": 6.4349, "cost": 213649, "chosen": true }, { "rows_to_scan": 119232, "filtering_effect": [ ], "final_filtering_effect": 0.1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 9, "resulting_rows": 11923, "cost": 1.13e8, "chosen": false } ] }, "condition_filtering_pct": 10, "rows_for_plan": 61042, "cost_for_plan": 247580, "semijoin_strategy_choice": [ { "strategy": "DuplicatesWeedout", "cost": 255239, "rows": 15531, "duplicate_tables_left": true, "chosen": true } ], "pruned_by_cost": true } ] }, { "plan_prefix": [ "`city`", "`country` `c`", "`country`" ], "table": "`depot`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "location_id", "usable": false, "chosen": false }, { "rows_to_scan": 119232, "filtering_effect": [ ], "final_filtering_effect": 0.1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 11923, "cost": 1.85e7, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 1.85e8, "cost_for_plan": 1.86e7, "semijoin_strategy_choice": [ ], "pruned_by_cost": true } ] } ] }, { "plan_prefix": [ "`city`" ], "table": "`location`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "ref", "index": "city_id", "rows": 6.1079, "cost": 21466, "chosen": true }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 94862, "cost_for_plan": 23060, "semijoin_strategy_choice": [ ], "pruned_by_heuristic": true }, { "plan_prefix": [ "`city`" ], "table": "`depot`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "location_id", "usable": false, "chosen": false }, { "rows_to_scan": 119232, "filtering_effect": [ ], "final_filtering_effect": 0.1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 11923, "cost": 1.85e7, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 1.85e8, "cost_for_plan": 1.85e7, "semijoin_strategy_choice": [ ], "pruned_by_cost": true } ] }, { "plan_prefix": [ ], "table": "`location`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "ref", "index": "city_id", "usable": false, "chosen": false }, { "rows_to_scan": 96688, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "resulting_rows": 96688, "cost": 9779.8, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 96688, "cost_for_plan": 9779.8, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`location`" ], "table": "`country` `c`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "rows_to_scan": 236, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 3, "resulting_rows": 236, "cost": 2.28e6, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 2.28e7, "cost_for_plan": 2.29e6, "semijoin_strategy_choice": [ ], "pruned_by_cost": true }, { "plan_prefix": [ "`location`" ], "table": "`country`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "rows_to_scan": 236, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 3, "resulting_rows": 236, "cost": 2.28e6, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 2.28e7, "cost_for_plan": 2.29e6, "semijoin_strategy_choice": [ ], "pruned_by_cost": true }, { "plan_prefix": [ "`location`" ], "table": "`city`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 50813, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 96688, "cost_for_plan": 60592, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`location`", "`city`" ], "table": "`country` `c`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 33841, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "added_to_eq_ref_extension": false }, { "plan_prefix": [ "`location`", "`city`" ], "table": "`country`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 33841, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "added_to_eq_ref_extension": false }, { "plan_prefix": [ "`location`", "`city`" ], "table": "`depot`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "location_id", "rows": 6.4349, "cost": 217762, "chosen": true }, { "rows_to_scan": 119232, "filtering_effect": [ ], "final_filtering_effect": 0.1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 6, "resulting_rows": 11923, "cost": 1.15e8, "chosen": false } ] }, "added_to_eq_ref_extension": false }, { "plan_prefix": [ "`location`", "`city`" ], "table": "`country` `c`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 33841, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 96688, "cost_for_plan": 94433, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`location`", "`city`", "`country` `c`" ], "table": "`country`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 33841, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "added_to_eq_ref_extension": true, "condition_filtering_pct": 100, "rows_for_plan": 96688, "cost_for_plan": 128274, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`location`", "`city`", "`country` `c`", "`country`" ], "table": "`depot`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "location_id", "rows": 6.4349, "cost": 217762, "chosen": true }, { "rows_to_scan": 119232, "filtering_effect": [ ], "final_filtering_effect": 0.1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 9, "resulting_rows": 11923, "cost": 1.15e8, "chosen": false } ] }, "added_to_eq_ref_extension": false }, { "plan_prefix": [ "`location`", "`city`", "`country` `c`", "`country`" ], "table": "`depot`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "location_id", "rows": 6.4349, "cost": 217762, "chosen": true }, { "rows_to_scan": 119232, "filtering_effect": [ ], "final_filtering_effect": 0.1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 9, "resulting_rows": 11923, "cost": 1.15e8, "chosen": false } ] }, "condition_filtering_pct": 10, "rows_for_plan": 62218, "cost_for_plan": 346036, "semijoin_strategy_choice": [ { "strategy": "DuplicatesWeedout", "cost": 357828, "rows": 55696, "duplicate_tables_left": true, "chosen": true } ], "pruned_by_cost": true } ] } ] }, { "plan_prefix": [ "`location`", "`city`" ], "table": "`depot`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "location_id", "rows": 6.4349, "cost": 217762, "chosen": true }, { "rows_to_scan": 119232, "filtering_effect": [ ], "final_filtering_effect": 0.1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 6, "resulting_rows": 11923, "cost": 1.15e8, "chosen": false } ] }, "condition_filtering_pct": 10, "rows_for_plan": 62218, "cost_for_plan": 278354, "semijoin_strategy_choice": [ ], "pruned_by_cost": true } ] }, { "plan_prefix": [ "`location`" ], "table": "`depot`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "location_id", "rows": 6.4349, "cost": 217762, "chosen": true }, { "rows_to_scan": 119232, "filtering_effect": [ ], "final_filtering_effect": 0.1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 3, "resulting_rows": 11923, "cost": 1.15e8, "chosen": false } ] }, "condition_filtering_pct": 10, "rows_for_plan": 62218, "cost_for_plan": 227541, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`location`", "`depot`" ], "table": "`country` `c`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "rows_to_scan": 236, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 4, "resulting_rows": 236, "cost": 1.47e6, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 1.47e7, "cost_for_plan": 1.7e6, "semijoin_strategy_choice": [ ], "pruned_by_cost": true }, { "plan_prefix": [ "`location`", "`depot`" ], "table": "`country`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "rows_to_scan": 236, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 4, "resulting_rows": 236, "cost": 1.47e6, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 1.47e7, "cost_for_plan": 1.7e6, "semijoin_strategy_choice": [ ], "pruned_by_cost": true }, { "plan_prefix": [ "`location`", "`depot`" ], "table": "`city`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 32697, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 62218, "cost_for_plan": 260239, "semijoin_strategy_choice": [ ], "pruned_by_cost": true } ] } ] }, { "plan_prefix": [ ], "table": "`depot`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "location_id", "usable": false, "chosen": false }, { "rows_to_scan": 119232, "filtering_effect": [ ], "final_filtering_effect": 0.1, "access_type": "scan", "resulting_rows": 11923, "cost": 11979, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 11923, "cost_for_plan": 11979, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`depot`" ], "table": "`country` `c`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "rows_to_scan": 236, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 236, "cost": 281388, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 2.81e6, "cost_for_plan": 293367, "semijoin_strategy_choice": [ ], "pruned_by_cost": true }, { "plan_prefix": [ "`depot`" ], "table": "`country`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "rows_to_scan": 236, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 236, "cost": 281388, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 2.81e6, "cost_for_plan": 293367, "semijoin_strategy_choice": [ ], "pruned_by_cost": true }, { "plan_prefix": [ "`depot`" ], "table": "`city`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "ref", "index": "country_id", "usable": false, "chosen": false }, { "rows_to_scan": 15531, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 15531, "cost": 1.85e7, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 1.85e8, "cost_for_plan": 1.85e7, "semijoin_strategy_choice": [ ], "pruned_by_cost": true }, { "plan_prefix": [ "`depot`" ], "table": "`location`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 7074.6, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 11923, "cost_for_plan": 19054, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`depot`", "`location`" ], "table": "`city`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 6266, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "added_to_eq_ref_extension": false }, { "plan_prefix": [ "`depot`", "`location`" ], "table": "`country` `c`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "rows_to_scan": 236, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 236, "cost": 281388, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 2.81e6, "cost_for_plan": 300442, "semijoin_strategy_choice": [ ], "pruned_by_cost": true }, { "plan_prefix": [ "`depot`", "`location`" ], "table": "`country`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "rows_to_scan": 236, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 236, "cost": 281388, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 2.81e6, "cost_for_plan": 300442, "semijoin_strategy_choice": [ ], "pruned_by_cost": true }, { "plan_prefix": [ "`depot`", "`location`" ], "table": "`city`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 6266, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 11923, "cost_for_plan": 25320, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`depot`", "`location`", "`city`" ], "table": "`country` `c`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 4173.1, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "added_to_eq_ref_extension": false }, { "plan_prefix": [ "`depot`", "`location`", "`city`" ], "table": "`country`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 4173.1, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "added_to_eq_ref_extension": false }, { "plan_prefix": [ "`depot`", "`location`", "`city`" ], "table": "`country` `c`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 4173.1, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 11923, "cost_for_plan": 29493, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`depot`", "`location`", "`city`", "`country` `c`" ], "table": "`country`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 4173.1, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "added_to_eq_ref_extension": true, "condition_filtering_pct": 100, "rows_for_plan": 11923, "cost_for_plan": 33666, "semijoin_strategy_choice": [ { "strategy": "DuplicatesWeedout", "cost": 36052, "rows": 11923, "duplicate_tables_left": true, "chosen": true } ], "chosen": true } ] } ] } ] } ] }, { "final_semijoin_strategy": "DuplicateWeedout" } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`location`.`id` = `depot`.`location_id`) and (`city`.`id` = `location`.`city_id`) and (`c`.`id` = `city`.`country_id`) and (`country`.`id` = `city`.`country_id`) and (`depot`.`deleted` = 0))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`depot`", "attached": "(`depot`.`deleted` = 0)" }, { "table": "`location`", "attached": "(`location`.`id` = `depot`.`location_id`)" }, { "table": "`city`", "attached": "(`city`.`id` = `location`.`city_id`)" }, { "table": "`country` `c`", "attached": "(`c`.`id` = `city`.`country_id`)" }, { "table": "`country`", "attached": "(`country`.`id` = `city`.`country_id`)" } ] } }, { "finalizing_table_conditions": [ { "table": "`depot`", "original_table_condition": "(`depot`.`deleted` = 0)", "final_table_condition ": "(`depot`.`deleted` = 0)" }, { "table": "`location`", "original_table_condition": "(`location`.`id` = `depot`.`location_id`)", "final_table_condition ": null }, { "table": "`city`", "original_table_condition": "(`city`.`id` = `location`.`city_id`)", "final_table_condition ": null }, { "table": "`country` `c`", "original_table_condition": "(`c`.`id` = `city`.`country_id`)", "final_table_condition ": null }, { "table": "`country`", "original_table_condition": "(`country`.`id` = `city`.`country_id`)", "final_table_condition ": null } ] }, { "refine_plan": [ { "creating_tmp_table": { "tmp_table_info": { "columns": 1, "row_length": 10, "key_length": 8, "unique_constraint": false, "makes_grouped_rows": false, "cannot_insert_duplicates": false, "location": "TempTable" } } }, { "table": "`depot`" }, { "table": "`location`" }, { "table": "`city`" }, { "table": "`country` `c`" }, { "table": "`country`" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] }