SELECT table_name, column_name FROM information_schema.columns where table_name="OPTIMIZER_TRACE" ; set optimizer_trace="enabled=on" ; 'optimizer_trace' ; set optimizer_trace="enabled=off" ; 'optimizer_trace' ; ; 1 ,2 ),(2 ,3 ); ; 1 ),(2 ); ; ; return 1 ; 1 ; 1 group by t1.b; set optimizer_trace="enabled=on" ; ; 1 2 ; "steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["view" : {"table" : "v1" ,"select_id" : 2 ,"algorithm" : "merged" "join_preparation" : {"select_id" : 2 ,"steps" : ["expanded_query" : "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1" "expanded_query" : "/* select#1 */ select v1.a AS a,v1.b AS b from v1" "join_optimization" : {"select_id" : 1 ,"steps" : ["condition_processing" : {"condition" : "WHERE" ,"original_condition" : "t1.a = 1" ,"steps" : ["transformation" : "equality_propagation" ,"resulting_condition" : "multiple equal(1, t1.a)" "transformation" : "constant_propagation" ,"resulting_condition" : "multiple equal(1, t1.a)" "transformation" : "trivial_condition_removal" ,"resulting_condition" : "multiple equal(1, t1.a)" "ref_optimizer_key_uses" : []"rows_estimation" : ["selectivity_for_indexes" : [],"selectivity_for_columns" : ["column_name" : "a" ,"ranges" : ["1 <= a <= 1" ],"selectivity_from_histogram" : 0 .5 "cond_selectivity" : 0 .5 "table" : "t1" ,"table_scan" : {"rows" : 2 ,"read_cost" : 0 .01028441 ,"read_and_compare_cost" : 0 .01034841 "table_dependencies" : ["table" : "t1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 2 ,"rows_after_filter" : 1 ,"rows_out" : 1 ,"cost" : 0 .01034841 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 1 ,"rows_out" : 1 ,"cost" : 0 .01034841 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 1 ,"cost_for_plan" : 0 .01034841 "best_join_order" : ["t1" ],"rows" : 1 ,"cost" : 0 .01034841 "substitute_best_equal" : {"condition" : "WHERE" ,"resulting_condition" : "t1.a = 1" "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "t1" ,"attached_condition" : "t1.a = 1" "make_join_readinfo" : []0 0 1 )q; 1 2 ; 1 )q {"steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["derived" : {"table" : "q" ,"select_id" : 2 ,"algorithm" : "merged" "join_preparation" : {"select_id" : 2 ,"steps" : ["expanded_query" : "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1" "expanded_query" : "/* select#1 */ select q.a AS a,q.b AS b from (/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1) q" "join_optimization" : {"select_id" : 1 ,"steps" : ["condition_processing" : {"condition" : "WHERE" ,"original_condition" : "t1.a = 1" ,"steps" : ["transformation" : "equality_propagation" ,"resulting_condition" : "multiple equal(1, t1.a)" "transformation" : "constant_propagation" ,"resulting_condition" : "multiple equal(1, t1.a)" "transformation" : "trivial_condition_removal" ,"resulting_condition" : "multiple equal(1, t1.a)" "ref_optimizer_key_uses" : []"rows_estimation" : ["selectivity_for_indexes" : [],"selectivity_for_columns" : ["column_name" : "a" ,"ranges" : ["1 <= a <= 1" ],"selectivity_from_histogram" : 0 .5 "cond_selectivity" : 0 .5 "table" : "t1" ,"table_scan" : {"rows" : 2 ,"read_cost" : 0 .01028441 ,"read_and_compare_cost" : 0 .01034841 "table_dependencies" : ["table" : "t1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 2 ,"rows_after_filter" : 1 ,"rows_out" : 1 ,"cost" : 0 .01034841 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 1 ,"rows_out" : 1 ,"cost" : 0 .01034841 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 1 ,"cost_for_plan" : 0 .01034841 "best_join_order" : ["t1" ],"rows" : 1 ,"cost" : 0 .01034841 "substitute_best_equal" : {"condition" : "WHERE" ,"resulting_condition" : "t1.a = 1" "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "t1" ,"attached_condition" : "t1.a = 1" "make_join_readinfo" : []0 0 ; 1 2 ; "steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["view" : {"table" : "v2" ,"select_id" : 2 ,"algorithm" : "materialized" "join_preparation" : {"select_id" : 2 ,"steps" : ["expanded_query" : "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1 group by t1.b" "expanded_query" : "/* select#1 */ select v2.a AS a,v2.b AS b from v2" "join_optimization" : {"select_id" : 1 ,"steps" : ["join_optimization" : {"select_id" : 2 ,"steps" : ["condition_processing" : {"condition" : "WHERE" ,"original_condition" : "t1.a = 1" ,"steps" : ["transformation" : "equality_propagation" ,"resulting_condition" : "multiple equal(1, t1.a)" "transformation" : "constant_propagation" ,"resulting_condition" : "multiple equal(1, t1.a)" "transformation" : "trivial_condition_removal" ,"resulting_condition" : "multiple equal(1, t1.a)" "ref_optimizer_key_uses" : []"rows_estimation" : ["selectivity_for_indexes" : [],"selectivity_for_columns" : ["column_name" : "a" ,"ranges" : ["1 <= a <= 1" ],"selectivity_from_histogram" : 0 .5 "cond_selectivity" : 0 .5 "table" : "t1" ,"table_scan" : {"rows" : 2 ,"read_cost" : 0 .01028441 ,"read_and_compare_cost" : 0 .01034841 "table_dependencies" : ["table" : "t1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 2 ,"rows_after_filter" : 1 ,"rows_out" : 1 ,"cost" : 0 .01034841 ,"index_only" : false,"chosen" : true,"use_tmp_table" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 1 ,"rows_out" : 1 ,"cost" : 0 .01034841 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 1 ,"cost_for_plan" : 0 .01034841 ,"cost_for_sorting" : 6 .301866 e-4 "materialized_output_cardinality" : {"join_output_cardinality" : 1 ,"estimation" : ["table" : "t1" ,"steps" : ["column" : "b" ,"cardinality" : 2 "cardinality" : 2 "check_split_materialized" : {"not_applicable" : "no candidate field can be accessed through ref" "best_join_order" : ["t1" ],"rows" : 1 ,"cost" : 0 .010978597 "substitute_best_equal" : {"condition" : "WHERE" ,"resulting_condition" : "t1.a = 1" "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "t1" ,"attached_condition" : "t1.a = 1" "make_join_readinfo" : []"test_if_skip_sort_order" : []"rows_estimation" : ["table" : "<derived2>" ,"table_scan" : {"rows" : 2 ,"read_cost" : 0 .010020701 ,"read_and_compare_cost" : 0 .010089369 "table_dependencies" : ["table" : "<derived2>" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "<derived2>" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 2 ,"rows_after_filter" : 2 ,"rows_out" : 2 ,"cost" : 0 .010089369 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 2 ,"rows_out" : 2 ,"cost" : 0 .010089369 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "<derived2>" ,"rows_for_plan" : 2 ,"cost_for_plan" : 0 .010089369 "best_join_order" : ["<derived2>" ],"rows" : 2 ,"cost" : 0 .010089369 "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "<derived2>" ,"attached_condition" : null"make_join_readinfo" : []0 0 ; ; ; ; 0 ,0 ),(1 ,1 ),(2 ,1 ),(3 ,2 ),(4 ,3 ),5 ,3 ),(6 ,3 ),(7 ,3 ),(8 ,3 ),(9 ,3 ); ; 0 ,0 ),(1 ,1 ),(2 ,1 ),(3 ,2 ),(4 ,3 ),5 ,3 ),(6 ,3 ),(7 ,3 ),(8 ,3 ),(9 ,3 ); ; ; ; ; ; 1 SIMPLE t2 ALL NULL NULL NULL NULL 10 "steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["view" : {"table" : "v2" ,"select_id" : 2 ,"algorithm" : "merged" "join_preparation" : {"select_id" : 2 ,"steps" : ["expanded_query" : "/* select#2 */ select t2.a AS a from t2" "expanded_query" : "/* select#1 */ select v2.a AS a from v2" "join_optimization" : {"select_id" : 1 ,"steps" : ["rows_estimation" : ["table" : "t2" ,"table_scan" : {"rows" : 10 ,"read_cost" : 0 .01127965 ,"read_and_compare_cost" : 0 .01159965 "table_dependencies" : ["table" : "t2" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t2" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 10 ,"rows_after_filter" : 10 ,"rows_out" : 10 ,"cost" : 0 .01159965 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 10 ,"rows_out" : 10 ,"cost" : 0 .01159965 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t2" ,"rows_for_plan" : 10 ,"cost_for_plan" : 0 .01159965 "best_join_order" : ["t2" ],"rows" : 10 ,"cost" : 0 .01159965 "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "t2" ,"attached_condition" : null"make_join_readinfo" : []0 0 ; 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 2 DERIVED t1 ALL NULL NULL NULL NULL 10 Using temporary; Using filesort "steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["view" : {"table" : "v1" ,"select_id" : 2 ,"algorithm" : "materialized" "join_preparation" : {"select_id" : 2 ,"steps" : ["expanded_query" : "/* select#2 */ select t1.a AS a from t1 group by t1.b" "expanded_query" : "/* select#1 */ select v1.a AS a from v1" "join_optimization" : {"select_id" : 1 ,"steps" : ["join_optimization" : {"select_id" : 2 ,"steps" : ["rows_estimation" : ["table" : "t1" ,"table_scan" : {"rows" : 10 ,"read_cost" : 0 .01127965 ,"read_and_compare_cost" : 0 .01159965 "table_dependencies" : ["table" : "t1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 10 ,"rows_after_filter" : 10 ,"rows_out" : 10 ,"cost" : 0 .01159965 ,"index_only" : false,"chosen" : true,"use_tmp_table" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 10 ,"rows_out" : 10 ,"cost" : 0 .01159965 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 10 ,"cost_for_plan" : 0 .01159965 ,"cost_for_sorting" : 0 .006368384 "materialized_output_cardinality" : {"join_output_cardinality" : 10 ,"estimation" : ["table" : "t1" ,"steps" : ["column" : "b" ,"cardinality" : 4 "cardinality" : 4 "post_group_cardinality" : 4 "check_split_materialized" : {"not_applicable" : "group list has no candidates" "best_join_order" : ["t1" ],"rows" : 10 ,"cost" : 0 .017968034 "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "t1" ,"attached_condition" : null"make_join_readinfo" : []"test_if_skip_sort_order" : []"rows_estimation" : ["table" : "<derived2>" ,"table_scan" : {"rows" : 4 ,"read_cost" : 0 .010041402 ,"read_and_compare_cost" : 0 .010178738 "table_dependencies" : ["table" : "<derived2>" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "<derived2>" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 4 ,"rows_after_filter" : 4 ,"rows_out" : 4 ,"cost" : 0 .010178738 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 4 ,"rows_out" : 4 ,"cost" : 0 .010178738 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "<derived2>" ,"rows_for_plan" : 4 ,"cost_for_plan" : 0 .010178738 "best_join_order" : ["<derived2>" ],"rows" : 4 ,"cost" : 0 .010178738 "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "<derived2>" ,"attached_condition" : null"make_join_readinfo" : []0 0 ; ; ; 0 ),(1 ),(2 ),(3 ),(4 ),(5 ),(6 ),(7 ),(8 ),(9 ); ; 10 + B.a, A.a*10 + B.a, A.a*10 + B.a from t0 A, t0 B; ; 10 + B.a, A.a*10 + B.a, A.a*10 + B.a from t0 A, t0 B; ; ; 2 and t2.a= t1.b; 1 SIMPLE t1 ALL a NULL NULL NULL 100 Using where1 SIMPLE t2 ref a a 5 test.t1.b 1 Using where; 2 and t2.a= t1.b {"steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["expanded_query" : "select t1.a AS a,t1.b AS b,t1.c AS c,t2.a AS a,t2.b AS b,t2.c AS c from t1 join t2 where t1.a = t2.b + 2 and t2.a = t1.b" "join_optimization" : {"select_id" : 1 ,"steps" : ["condition_processing" : {"condition" : "WHERE" ,"original_condition" : "t1.a = t2.b + 2 and t2.a = t1.b" ,"steps" : ["transformation" : "equality_propagation" ,"resulting_condition" : "t1.a = t2.b + 2 and multiple equal(t2.a, t1.b)" "transformation" : "constant_propagation" ,"resulting_condition" : "t1.a = t2.b + 2 and multiple equal(t2.a, t1.b)" "transformation" : "trivial_condition_removal" ,"resulting_condition" : "t1.a = t2.b + 2 and multiple equal(t2.a, t1.b)" "ref_optimizer_key_uses" : ["table" : "t1" ,"index" : "a" ,"field" : "a" ,"equals" : "t2.b + 2" ,"null_rejecting" : true"table" : "t2" ,"index" : "a" ,"field" : "a" ,"equals" : "t1.b" ,"null_rejecting" : true"rows_estimation" : ["table" : "t1" ,"table_scan" : {"rows" : 100 ,"read_cost" : 0 .0224761 ,"read_and_compare_cost" : 0 .0256761 "table" : "t2" ,"table_scan" : {"rows" : 100 ,"read_cost" : 0 .0224761 ,"read_and_compare_cost" : 0 .0256761 "table_dependencies" : ["table" : "t1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"table" : "t2" ,"row_may_be_null" : false,"map_bit" : 1 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 100 ,"rows_after_filter" : 100 ,"rows_out" : 100 ,"cost" : 0 .0256761 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 100 ,"rows_out" : 100 ,"cost" : 0 .0256761 ,"uses_join_buffering" : false"best_access_path" : {"table" : "t2" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 100 ,"rows_after_filter" : 100 ,"rows_out" : 100 ,"cost" : 0 .0256761 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 100 ,"rows_out" : 100 ,"cost" : 0 .0256761 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 100 ,"cost_for_plan" : 0 .0256761 ,"rest_of_plan" : ["plan_prefix" : "t1" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t2" ,"plan_details" : {"record_count" : 100 "considered_access_paths" : ["access_type" : "ref" ,"index" : "a" ,"used_range_estimates" : false,"reason" : "not available" ,"rows" : 1 ,"cost" : 0 .1821659 ,"chosen" : true"access_type" : "scan_with_join_cache" ,"rows" : 100 ,"rows_after_filter" : 100 ,"rows_out" : 1 ,"cost" : 0 .9604227 ,"cost_without_join_buffer" : 2 .56761 ,"index_only" : false,"chosen" : false"chosen_access_method" : {"type" : "ref" ,"index" : "a" ,"rows_read" : 1 ,"rows_out" : 1 ,"cost" : 0 .1821659 ,"uses_join_buffering" : false"plan_prefix" : "t1" ,"table" : "t2" ,"rows_for_plan" : 100 ,"cost_for_plan" : 0 .207842 "plan_prefix" : "" ,"table" : "t2" ,"rows_for_plan" : 100 ,"cost_for_plan" : 0 .0256761 ,"rest_of_plan" : ["plan_prefix" : "t2" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 100 "considered_access_paths" : ["access_type" : "ref" ,"index" : "a" ,"used_range_estimates" : false,"reason" : "not available" ,"rows" : 1 ,"cost" : 0 .1821659 ,"chosen" : true"access_type" : "scan_with_join_cache" ,"rows" : 100 ,"rows_after_filter" : 100 ,"rows_out" : 1 ,"cost" : 0 .9604227 ,"cost_without_join_buffer" : 2 .56761 ,"index_only" : false,"chosen" : false"chosen_access_method" : {"type" : "ref" ,"index" : "a" ,"rows_read" : 1 ,"rows_out" : 1 ,"cost" : 0 .1821659 ,"uses_join_buffering" : false"plan_prefix" : "t2" ,"table" : "t1" ,"rows_for_plan" : 100 ,"cost_for_plan" : 0 .207842 ,"pruned_by_cost" : true,"current_cost" : 0 .207842 ,"best_cost" : 0 .207842 "best_join_order" : ["t1" , "t2" ],"rows" : 100 ,"cost" : 0 .207842 "substitute_best_equal" : {"condition" : "WHERE" ,"resulting_condition" : "t2.a = t1.b and t1.a = t2.b + 2" "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "t1" ,"attached_condition" : "t1.b is not null" "table" : "t2" ,"attached_condition" : "t1.a = t2.b + 2" "make_join_readinfo" : []0 0 ; min max optimizationNOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a)); 4 )+1 from seq_1_to_65536; ; ; 1 SIMPLE t1 range NULL a 4 NULL 5 Using index for group-by; "steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["expanded_query" : "select distinct t1.a AS a from t1" "join_optimization" : {"select_id" : 1 ,"steps" : ["rows_estimation" : ["table" : "t1" ,"range_analysis" : {"table_scan" : {"rows" : 65536 ,"cost" : 10 .29477568 "potential_range_indexes" : ["index" : "PRIMARY" ,"usable" : false,"cause" : "not applicable" "index" : "a" ,"usable" : true,"key_parts" : ["a" ]"best_covering_index_scan" : {"index" : "a" ,"cost" : 9 .118658462 ,"chosen" : true"group_index_range" : {"distinct_query" : true,"potential_group_range_indexes" : ["index" : "a" ,"covering" : true,"rows" : 5 ,"cost" : 0 .004191135 "best_group_range_summary" : {"type" : "index_group" ,"index" : "a" ,"min_max_arg" : null,"min_aggregate" : false,"max_aggregate" : false,"distinct_aggregate" : false,"rows" : 5 ,"cost" : 0 .004191135 ,"key_parts_used_for_access" : ["a" ],"ranges" : [],"chosen" : true"chosen_range_access_summary" : {"range_access_plan" : {"type" : "index_group" ,"index" : "a" ,"min_max_arg" : null,"min_aggregate" : false,"max_aggregate" : false,"distinct_aggregate" : false,"rows" : 5 ,"cost" : 0 .004191135 ,"key_parts_used_for_access" : ["a" ],"ranges" : []"rows_for_plan" : 5 ,"cost_for_plan" : 0 .004191135 ,"chosen" : true"table_dependencies" : ["table" : "t1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "index_merge" ,"rows" : 5 ,"rows_after_filter" : 5 ,"rows_out" : 5 ,"cost" : 0 .004191135 ,"chosen" : true"chosen_access_method" : {"type" : "index_merge" ,"rows_read" : 5 ,"rows_out" : 5 ,"cost" : 0 .004191135 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 5 ,"cost_for_plan" : 0 .004191135 "best_join_order" : ["t1" ],"rows" : 5 ,"cost" : 0 .004191135 "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "t1" ,"attached_condition" : null"make_join_readinfo" : []"test_if_skip_sort_order" : []0 0 ; and MIN /MAX function; 1 ,1 ,1 ,1 ), (2 ,2 ,2 ,2 ), (3 ,3 ,3 ,3 ), (4 ,4 ,4 ,4 ), (1 ,0 ,1 ,1 ), (3 ,2 ,3 ,3 ), (4 ,5 ,4 ,4 ); ; MIN (d) FROM t1 where b=2 and c=3 group by a; 1 SIMPLE t1 range NULL a 20 NULL 7 Using where; Using index for group-by set statement optimizer_scan_setup_cost=0 for EXPLAIN SELECT MIN (d) FROM t1 where b=2 and c=3 ; 1 SIMPLE t1 index NULL a 20 NULL 7 Using where; Using index ; set statement optimizer_scan_setup_cost=0 for EXPLAIN SELECT MIN (d) FROM t1 where b=2 and c=3 "steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["expanded_query" : "select min(t1.d) AS `MIN(d)` from t1 where t1.b = 2 and t1.c = 3 group by t1.a" "join_optimization" : {"select_id" : 1 ,"steps" : ["condition_processing" : {"condition" : "WHERE" ,"original_condition" : "t1.b = 2 and t1.c = 3" ,"steps" : ["transformation" : "equality_propagation" ,"resulting_condition" : "multiple equal(2, t1.b) and multiple equal(3, t1.c)" "transformation" : "constant_propagation" ,"resulting_condition" : "multiple equal(2, t1.b) and multiple equal(3, t1.c)" "transformation" : "trivial_condition_removal" ,"resulting_condition" : "multiple equal(2, t1.b) and multiple equal(3, t1.c)" "ref_optimizer_key_uses" : []"rows_estimation" : ["table" : "t1" ,"range_analysis" : {"table_scan" : {"rows" : 7 ,"cost" : 0 .001130435 "potential_range_indexes" : ["index" : "a" ,"usable" : true,"key_parts" : ["a" , "b" , "c" , "d" ]"best_covering_index_scan" : {"index" : "a" ,"cost" : 0 .001758432 ,"chosen" : false,"cause" : "cost" "setup_range_conditions" : [],"analyzing_range_alternatives" : {"range_scan_alternatives" : [],"analyzing_roworder_intersect" : {"cause" : "too few roworder scans" "group_index_range" : {"potential_group_range_indexes" : ["index" : "a" ,"covering" : true,"ranges" : ["(2,3) <= (b,c) <= (2,3)" ],"rows" : 7 ,"cost" : 0 .004425189 "best_group_range_summary" : {"type" : "index_group" ,"index" : "a" ,"min_max_arg" : "d" ,"min_aggregate" : true,"max_aggregate" : false,"distinct_aggregate" : false,"rows" : 7 ,"cost" : 0 .004425189 ,"key_parts_used_for_access" : ["a" , "b" , "c" ],"ranges" : ["(2,3) <= (b,c) <= (2,3)" ],"chosen" : false,"cause" : "cost" "selectivity_for_indexes" : [],"selectivity_for_columns" : ["column_name" : "b" ,"ranges" : ["2 <= b <= 2" ],"selectivity_from_histogram" : 0 .285714286 "column_name" : "c" ,"ranges" : ["3 <= c <= 3" ],"selectivity_from_histogram" : 0 .285714286 "cond_selectivity" : 0 .081632653 "table_dependencies" : ["table" : "t1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 7 ,"rows_after_filter" : 1 ,"rows_out" : 0 .571428573 ,"cost" : 0 .001758432 ,"index_only" : true,"chosen" : true,"use_tmp_table" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 1 ,"rows_out" : 0 .571428573 ,"cost" : 0 .001758432 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 0 .571428573 ,"cost_for_plan" : 0 .001758432 ,"pushdown_cond_selectivity" : 0 .571428573 ,"filtered" : 8 .163265322 ,"rows_out" : 0 .571428573 ,"cost_for_sorting" : 3 .585611 e-4 "best_join_order" : ["t1" ],"rows" : 0 .571428573 ,"cost" : 0 .002116993 "substitute_best_equal" : {"condition" : "WHERE" ,"resulting_condition" : "t1.b = 2 and t1.c = 3" "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "t1" ,"attached_condition" : "t1.b = 2 and t1.c = 3" "make_join_readinfo" : []"test_if_skip_sort_order" : ["reconsidering_access_paths_for_index_ordering" : {"clause" : "GROUP BY" ,"table" : "t1" ,"rows_estimation" : 1 ,"filesort_cost" : 4 .579083 e-5 ,"read_cost" : 0 .001804223 ,"filesort_type" : "priority_queue with addon fields" ,"fanout" : 1 ,"possible_keys" : ["index" : "a" ,"can_resolve_order" : true,"direction" : 1 ,"rows_to_examine" : 7 ,"range_scan" : false,"scan_cost" : 0 .001758432 ,"chosen" : true"prepare_sum_aggregators" : {"function" : "min(t1.d)" ,"aggregator_type" : "simple" 0 0 ; NOT NULL, a DATE, KEY(id,a)); 1 ,'2001-01-01' ),(1 ,'2001-01-02' ),1 ,'2001-01-03' ),(1 ,'2001-01-04' ),2 ,'2001-01-01' ),(2 ,'2001-01-02' ),2 ,'2001-01-03' ),(2 ,'2001-01-04' ),3 ,'2001-01-01' ),(3 ,'2001-01-02' ),3 ,'2001-01-03' ),(3 ,'2001-01-04' ),4 ,'2001-01-01' ),(4 ,'2001-01-02' ),4 ,'2001-01-03' ),(4 ,'2001-01-04' ); set optimizer_trace='enabled=on' ; MIN (a),MAX (a) FROM t1 WHERE a>=20010104 e0 GROUP BY id; 1 SIMPLE t1 range NULL id 8 NULL 9 Using where; Using index for group-by ; MIN (a),MAX (a) FROM t1 WHERE a>=20010104 e0 GROUP BY id {"steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["expanded_query" : "select t1.`id` AS `id`,min(t1.a) AS `MIN(a)`,max(t1.a) AS `MAX(a)` from t1 where t1.a >= 20010104e0 group by t1.`id`" "join_optimization" : {"select_id" : 1 ,"steps" : ["condition_processing" : {"condition" : "WHERE" ,"original_condition" : "t1.a >= 20010104e0" ,"steps" : ["transformation" : "equality_propagation" ,"resulting_condition" : "t1.a >= 20010104e0" "transformation" : "constant_propagation" ,"resulting_condition" : "t1.a >= 20010104e0" "transformation" : "trivial_condition_removal" ,"resulting_condition" : "t1.a >= 20010104e0" "ref_optimizer_key_uses" : []"rows_estimation" : ["table" : "t1" ,"range_analysis" : {"table_scan" : {"rows" : 16 ,"cost" : 0 .01253808 "potential_range_indexes" : ["index" : "id" ,"usable" : true,"key_parts" : ["id" , "a" ]"best_covering_index_scan" : {"index" : "id" ,"cost" : 0 .008002862 ,"chosen" : true"setup_range_conditions" : [],"analyzing_range_alternatives" : {"range_scan_alternatives" : [],"analyzing_roworder_intersect" : {"cause" : "too few roworder scans" "group_index_range" : {"potential_group_range_indexes" : ["index" : "id" ,"covering" : true,"ranges" : ["(2001-01-04) <= (a)" ],"rows" : 9 ,"cost" : 0 .005620843 "best_group_range_summary" : {"type" : "index_group" ,"index" : "id" ,"min_max_arg" : "a" ,"min_aggregate" : true,"max_aggregate" : true,"distinct_aggregate" : false,"rows" : 9 ,"cost" : 0 .005620843 ,"key_parts_used_for_access" : ["id" ],"ranges" : ["(2001-01-04) <= (a)" ],"chosen" : true"chosen_range_access_summary" : {"range_access_plan" : {"type" : "index_group" ,"index" : "id" ,"min_max_arg" : "a" ,"min_aggregate" : true,"max_aggregate" : true,"distinct_aggregate" : false,"rows" : 9 ,"cost" : 0 .005620843 ,"key_parts_used_for_access" : ["id" ],"ranges" : ["(2001-01-04) <= (a)" ]"rows_for_plan" : 9 ,"cost_for_plan" : 0 .005620843 ,"chosen" : true"table_dependencies" : ["table" : "t1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "index_merge" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .005620843 ,"chosen" : true,"use_tmp_table" : true"chosen_access_method" : {"type" : "index_merge" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .005620843 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 9 ,"cost_for_plan" : 0 .005620843 ,"cost_for_sorting" : 0 .005728198 "best_join_order" : ["t1" ],"rows" : 9 ,"cost" : 0 .011349041 "substitute_best_equal" : {"condition" : "WHERE" ,"resulting_condition" : "t1.a >= 20010104e0" "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "t1" ,"attached_condition" : "t1.a >= 20010104e0" "make_join_readinfo" : []"test_if_skip_sort_order" : []"prepare_sum_aggregators" : {"function" : "min(t1.a)" ,"aggregator_type" : "simple" "prepare_sum_aggregators" : {"function" : "max(t1.a)" ,"aggregator_type" : "simple" 0 0 20010104 e0 GROUP BY id; 1 SIMPLE t1 range NULL id 8 NULL 9 Using where; Using index for group-by ; 20010104 e0 GROUP BY id {"steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["expanded_query" : "select t1.`id` AS `id`,t1.a AS a from t1 where t1.a = 20010104e0 group by t1.`id`" "join_optimization" : {"select_id" : 1 ,"steps" : ["condition_processing" : {"condition" : "WHERE" ,"original_condition" : "t1.a = 20010104e0" ,"steps" : ["transformation" : "equality_propagation" ,"resulting_condition" : "t1.a = 20010104e0" "transformation" : "constant_propagation" ,"resulting_condition" : "t1.a = 20010104e0" "transformation" : "trivial_condition_removal" ,"resulting_condition" : "t1.a = 20010104e0" "ref_optimizer_key_uses" : []"rows_estimation" : ["table" : "t1" ,"range_analysis" : {"table_scan" : {"rows" : 16 ,"cost" : 0 .01253808 "potential_range_indexes" : ["index" : "id" ,"usable" : true,"key_parts" : ["id" , "a" ]"best_covering_index_scan" : {"index" : "id" ,"cost" : 0 .008002862 ,"chosen" : true"setup_range_conditions" : [],"analyzing_range_alternatives" : {"range_scan_alternatives" : [],"analyzing_roworder_intersect" : {"cause" : "too few roworder scans" "group_index_range" : {"potential_group_range_indexes" : ["index" : "id" ,"covering" : true,"ranges" : ["(2001-01-04) <= (a) <= (2001-01-04)" ],"rows" : 9 ,"cost" : 0 .005620843 "best_group_range_summary" : {"type" : "index_group" ,"index" : "id" ,"min_max_arg" : null,"min_aggregate" : false,"max_aggregate" : false,"distinct_aggregate" : false,"rows" : 9 ,"cost" : 0 .005620843 ,"key_parts_used_for_access" : ["id" , "a" ],"ranges" : ["(2001-01-04) <= (a) <= (2001-01-04)" ],"chosen" : true"chosen_range_access_summary" : {"range_access_plan" : {"type" : "index_group" ,"index" : "id" ,"min_max_arg" : null,"min_aggregate" : false,"max_aggregate" : false,"distinct_aggregate" : false,"rows" : 9 ,"cost" : 0 .005620843 ,"key_parts_used_for_access" : ["id" , "a" ],"ranges" : ["(2001-01-04) <= (a) <= (2001-01-04)" ]"rows_for_plan" : 9 ,"cost_for_plan" : 0 .005620843 ,"chosen" : true"table_dependencies" : ["table" : "t1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "index_merge" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .005620843 ,"chosen" : true,"use_tmp_table" : true"chosen_access_method" : {"type" : "index_merge" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .005620843 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 9 ,"cost_for_plan" : 0 .005620843 ,"cost_for_sorting" : 0 .005728198 "best_join_order" : ["t1" ],"rows" : 9 ,"cost" : 0 .011349041 "substitute_best_equal" : {"condition" : "WHERE" ,"resulting_condition" : "t1.a = 20010104e0" "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "t1" ,"attached_condition" : "t1.a = 20010104e0" "make_join_readinfo" : []"test_if_skip_sort_order" : []0 0 ; not null,100 ),; 'filler-dataaa' from seq_0_to_999; set a=1 where pk between 0 and 180 ; set b=2 where pk between 0 and 20 ; ; 1 and b=2 order by c limit 1 ; 1 SIMPLE t1 ref a_c,a_b a_b 10 const,const 21 Using where; Using filesort set b=2 where pk between 20 and 40 ; set optimizer_trace='enabled=on' ; 1 and b=2 order by c limit 1 ; 1 SIMPLE t1 range a_c,a_b a_c 5 NULL 180 Using where; 1 and b=2 order by c limit 1 {"steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["expanded_query" : "select t1.pk AS pk,t1.a AS a,t1.b AS b,t1.c AS c,t1.filler AS filler from t1 where t1.a = 1 and t1.b = 2 order by t1.c limit 1" "join_optimization" : {"select_id" : 1 ,"steps" : ["condition_processing" : {"condition" : "WHERE" ,"original_condition" : "t1.a = 1 and t1.b = 2" ,"steps" : ["transformation" : "equality_propagation" ,"resulting_condition" : "multiple equal(1, t1.a) and multiple equal(2, t1.b)" "transformation" : "constant_propagation" ,"resulting_condition" : "multiple equal(1, t1.a) and multiple equal(2, t1.b)" "transformation" : "trivial_condition_removal" ,"resulting_condition" : "multiple equal(1, t1.a) and multiple equal(2, t1.b)" "ref_optimizer_key_uses" : ["table" : "t1" ,"index" : "a_c" ,"field" : "a" ,"equals" : "1" ,"null_rejecting" : true"table" : "t1" ,"index" : "a_b" ,"field" : "a" ,"equals" : "1" ,"null_rejecting" : true"table" : "t1" ,"index" : "a_b" ,"field" : "b" ,"equals" : "2" ,"null_rejecting" : true"rows_estimation" : ["table" : "t1" ,"range_analysis" : {"table_scan" : {"rows" : 1000 ,"cost" : 0 .1731718 "potential_range_indexes" : ["index" : "c" ,"usable" : false,"cause" : "not applicable" "index" : "a_c" ,"usable" : true,"key_parts" : ["a" , "c" ]"index" : "a_b" ,"usable" : true,"key_parts" : ["a" , "b" ]"setup_range_conditions" : [],"analyzing_range_alternatives" : {"range_scan_alternatives" : ["index" : "a_c" ,"ranges" : ["(1) <= (a) <= (1)" ],"rowid_ordered" : false,"using_mrr" : false,"index_only" : false,"rows" : 180 ,"cost" : 0 .223437104 ,"chosen" : false,"cause" : "cost" "index" : "a_b" ,"ranges" : ["(1,2) <= (a,b) <= (1,2)" ],"rowid_ordered" : true,"using_mrr" : false,"index_only" : false,"rows" : 41 ,"cost" : 0 .051929313 ,"chosen" : true"analyzing_roworder_intersect" : {"cause" : "too few roworder scans" "group_index_range" : {"chosen" : false,"cause" : "no group by or distinct" "chosen_range_access_summary" : {"range_access_plan" : {"type" : "range_scan" ,"index" : "a_b" ,"rows" : 41 ,"ranges" : ["(1,2) <= (a,b) <= (1,2)" ]"rows_for_plan" : 41 ,"cost_for_plan" : 0 .051929313 ,"chosen" : true"table" : "t1" ,"rowid_filters" : ["key" : "a_b" ,"build_cost" : 0 .005839142 ,"rows" : 41 "key" : "a_c" ,"build_cost" : 0 .023974342 ,"rows" : 180 "selectivity_for_indexes" : ["index_name" : "a_b" ,"selectivity_from_index" : 0 .041 "selectivity_for_columns" : ["column_name" : "a" ,"ranges" : ["1 <= a <= 1" ],"selectivity_from_histogram" : 0 .181 "column_name" : "b" ,"ranges" : ["2 <= b <= 2" ],"selectivity_from_histogram" : 0 .021 "cond_selectivity" : 0 .041 "table_dependencies" : ["table" : "t1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "ref" ,"index" : "a_c" ,"used_range_estimates" : true,"rows" : 180 ,"cost" : 0 .222886962 ,"chosen" : true"access_type" : "ref" ,"index" : "a_b" ,"used_range_estimates" : true,"rows" : 41 ,"cost" : 0 .051379171 ,"chosen" : true"type" : "scan" ,"chosen" : false,"cause" : "cost" "chosen_access_method" : {"type" : "ref" ,"index" : "a_b" ,"rows_read" : 41 ,"rows_out" : 41 ,"cost" : 0 .051379171 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 41 ,"cost_for_plan" : 0 .051379171 "best_join_order" : ["t1" ],"rows" : 41 ,"cost" : 0 .051379171 "substitute_best_equal" : {"condition" : "WHERE" ,"resulting_condition" : "t1.a = 1 and t1.b = 2" "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "t1" ,"attached_condition" : null"make_join_readinfo" : []"test_if_skip_sort_order" : ["reconsidering_access_paths_for_index_ordering" : {"clause" : "ORDER BY" ,"table" : "t1" ,"rows_estimation" : 41 ,"filesort_cost" : 9 .387121 e-4 ,"read_cost" : 0 .052317883 ,"filesort_type" : "priority_queue with addon fields" ,"fanout" : 1 ,"possible_keys" : ["index" : "c" ,"can_resolve_order" : true,"direction" : 1 ,"rows_to_examine" : 24 ,"range_scan" : false,"scan_cost" : 0 .030403398 ,"chosen" : true"index" : "a_c" ,"can_resolve_order" : true,"direction" : 1 ,"rows_to_examine" : 4 .390243902 ,"range_scan" : true,"scan_cost" : 0 .023390252 ,"chosen" : true"index" : "a_b" ,"can_resolve_order" : false,"cause" : "not usable index for the query" "table" : "t1" ,"range_analysis" : {"potential_range_indexes" : ["index" : "c" ,"usable" : false,"cause" : "not applicable" "index" : "a_c" ,"usable" : true,"key_parts" : ["a" , "c" ]"index" : "a_b" ,"usable" : false,"cause" : "not applicable" "setup_range_conditions" : [],"analyzing_range_alternatives" : {"range_scan_alternatives" : ["index" : "a_c" ,"ranges" : ["(1) <= (a) <= (1)" ],"rowid_ordered" : false,"using_mrr" : false,"index_only" : false,"rows" : 180 ,"cost" : 0 .223437104 ,"cost_with_limit" : 0 .002574553 ,"chosen" : true"analyzing_roworder_intersect" : {"cause" : "too few roworder scans" "group_index_range" : {"chosen" : false,"cause" : "no group by or distinct" "chosen_range_access_summary" : {"range_access_plan" : {"type" : "range_scan" ,"index" : "a_c" ,"rows" : 180 ,"ranges" : ["(1) <= (a) <= (1)" ]"rows_for_plan" : 180 ,"cost_for_plan" : 0 .223437104 ,"chosen" : true0 0 ; ; 0 ),(1 ),(2 ),(3 ); ; 1 ,2 ); 1 ,3 ); set optimizer_trace='enabled=on' ; ; ; ; ; 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 ; "steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["expanded_query" : "select t1.a AS a from (t1 left join t2 on(t1.a = t2.a))" "join_optimization" : {"select_id" : 1 ,"steps" : ["build_equal_items" : {"condition" : "ON expr" ,"attached_to" : "t2" ,"resulting_condition" : "multiple equal(t1.a, t2.a)" "ref_optimizer_key_uses" : ["table" : "t2" ,"index" : "PRIMARY" ,"field" : "a" ,"equals" : "t1.a" ,"null_rejecting" : true"eliminated_tables" : ["t2" ]"rows_estimation" : ["table" : "t1" ,"table_scan" : {"rows" : 4 ,"read_cost" : 0 .01053322 ,"read_and_compare_cost" : 0 .01066122 "table" : "t2" ,"rows" : 1 ,"cost" : 1 ,"table_type" : "const" "table_dependencies" : ["table" : "t1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"table" : "t2" ,"row_may_be_null" : true,"map_bit" : 1 ,"depends_on_map_bits" : [0 ]"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 4 ,"rows_after_filter" : 4 ,"rows_out" : 4 ,"cost" : 0 .01066122 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 4 ,"rows_out" : 4 ,"cost" : 0 .01066122 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 4 ,"cost_for_plan" : 0 .01066122 "best_join_order" : ["t2" , "t1" ],"rows" : 4 ,"cost" : 0 .01066122 "substitute_best_equal" : {"condition" : "WHERE" ,"resulting_condition" : "1" "attaching_conditions_to_tables" : {"attached_conditions_computation" : ["condition_on_constant_tables" : "1" ,"computing_condition" : []"attached_conditions_summary" : ["table" : "t1" ,"attached_condition" : null"make_join_readinfo" : []0 0 ; 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; "steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["expanded_query" : "select t1.a AS a,t2.a AS a,t2.b AS b from (t1 left join t2 on(t2.a = t1.a))" "join_optimization" : {"select_id" : 1 ,"steps" : ["build_equal_items" : {"condition" : "ON expr" ,"attached_to" : "t2" ,"resulting_condition" : "multiple equal(t2.a, t1.a)" "ref_optimizer_key_uses" : ["table" : "t2" ,"index" : "PRIMARY" ,"field" : "a" ,"equals" : "t1.a" ,"null_rejecting" : true"eliminated_tables" : []"rows_estimation" : ["table" : "t1" ,"table_scan" : {"rows" : 4 ,"read_cost" : 0 .01053322 ,"read_and_compare_cost" : 0 .01066122 "table" : "t2" ,"table_scan" : {"rows" : 2 ,"read_cost" : 0 .01028441 ,"read_and_compare_cost" : 0 .01034841 "table_dependencies" : ["table" : "t1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"table" : "t2" ,"row_may_be_null" : true,"map_bit" : 1 ,"depends_on_map_bits" : [0 ]"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 4 ,"rows_after_filter" : 4 ,"rows_out" : 4 ,"cost" : 0 .01066122 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 4 ,"rows_out" : 4 ,"cost" : 0 .01066122 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 4 ,"cost_for_plan" : 0 .01066122 ,"rest_of_plan" : ["plan_prefix" : "t1" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t2" ,"plan_details" : {"record_count" : 4 "considered_access_paths" : ["access_type" : "eq_ref" ,"index" : "PRIMARY" ,"rows" : 1 ,"cost" : 0 .007120904 ,"chosen" : true"type" : "scan" ,"chosen" : false,"cause" : "cost" "chosen_access_method" : {"type" : "eq_ref" ,"index" : "PRIMARY" ,"rows_read" : 1 ,"rows_out" : 1 ,"cost" : 0 .007120904 ,"uses_join_buffering" : false"plan_prefix" : "t1" ,"table" : "t2" ,"rows_for_plan" : 4 ,"cost_for_plan" : 0 .017782124 "best_join_order" : ["t1" , "t2" ],"rows" : 4 ,"cost" : 0 .017782124 "substitute_best_equal" : {"condition" : "WHERE" ,"resulting_condition" : "1" "substitute_best_equal" : {"condition" : "ON expr" ,"attached_to" : "t2" ,"resulting_condition" : "t2.a = t1.a" "attaching_conditions_to_tables" : {"attached_conditions_computation" : ["condition_on_constant_tables" : "1" ,"computing_condition" : []"attached_conditions_summary" : ["table" : "t1" ,"attached_condition" : null"table" : "t2" ,"attached_condition" : "trigcond(trigcond(t1.a is not null))" "make_join_readinfo" : []0 0 and t3.a=t1.a; 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 ; and t3.a=t1.a {"steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["expanded_query" : "select t1.a AS a from (t1 left join (t2 join t3 on(t2.b = t3.b)) on(t2.a = t1.a and t3.a = t1.a))" "join_optimization" : {"select_id" : 1 ,"steps" : ["build_equal_items" : {"condition" : "ON expr" ,"attached_to" : "t3" ,"resulting_condition" : "multiple equal(t2.a, t1.a, t3.a) and multiple equal(t2.b, t3.b)" "ref_optimizer_key_uses" : ["table" : "t2" ,"index" : "PRIMARY" ,"field" : "a" ,"equals" : "t1.a" ,"null_rejecting" : true"table" : "t2" ,"index" : "PRIMARY" ,"field" : "a" ,"equals" : "t3.a" ,"null_rejecting" : true"table" : "t3" ,"index" : "PRIMARY" ,"field" : "a" ,"equals" : "t2.a" ,"null_rejecting" : true"table" : "t3" ,"index" : "PRIMARY" ,"field" : "a" ,"equals" : "t1.a" ,"null_rejecting" : true"eliminated_tables" : ["t3" , "t2" ]"rows_estimation" : ["table" : "t1" ,"table_scan" : {"rows" : 4 ,"read_cost" : 0 .01053322 ,"read_and_compare_cost" : 0 .01066122 "table" : "t2" ,"rows" : 1 ,"cost" : 1 ,"table_type" : "const" "table" : "t3" ,"rows" : 1 ,"cost" : 1 ,"table_type" : "const" "table_dependencies" : ["table" : "t1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"table" : "t2" ,"row_may_be_null" : true,"map_bit" : 1 ,"depends_on_map_bits" : [0 ]"table" : "t3" ,"row_may_be_null" : true,"map_bit" : 2 ,"depends_on_map_bits" : [0 ]"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 4 ,"rows_after_filter" : 4 ,"rows_out" : 4 ,"cost" : 0 .01066122 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 4 ,"rows_out" : 4 ,"cost" : 0 .01066122 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 4 ,"cost_for_plan" : 0 .01066122 "best_join_order" : ["t3" , "t2" , "t1" ],"rows" : 4 ,"cost" : 0 .01066122 "substitute_best_equal" : {"condition" : "WHERE" ,"resulting_condition" : "1" "attaching_conditions_to_tables" : {"attached_conditions_computation" : ["condition_on_constant_tables" : "1" ,"computing_condition" : []"attached_conditions_summary" : ["table" : "t1" ,"attached_condition" : null"make_join_readinfo" : []0 0 ; ; ; ; ; set optimizer_trace='enabled=on' ; ; 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100 .00 1 PRIMARY t2 ALL NULL NULL NULL NULL 10 10 .00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`p` = `test`.`t1`.`a`; ; 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100 .00 1 PRIMARY t2 ALL NULL NULL NULL NULL 101 0 .99 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`p` = `test`.`t1`.`a`; "steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["join_preparation" : {"select_id" : 2 ,"steps" : ["transformation" : {"select_id" : 2 ,"from" : "IN (SELECT)" ,"to" : "semijoin" ,"chosen" : true"expanded_query" : "/* select#2 */ select t2.p from t2" "expanded_query" : "/* select#1 */ select t1.a AS a,t1.b AS b from t1 where t1.a in (/* select#2 */ select t2.p from t2)" "join_optimization" : {"select_id" : 1 ,"steps" : ["transformation" : {"select_id" : 2 ,"from" : "IN (SELECT)" ,"to" : "materialization" ,"sjm_scan_allowed" : true,"possible" : true"transformation" : {"select_id" : 2 ,"from" : "IN (SELECT)" ,"to" : "semijoin" ,"converted_to_semi_join" : true"condition_processing" : {"condition" : "WHERE" ,"original_condition" : "1 and t1.a = t2.p" ,"steps" : ["transformation" : "equality_propagation" ,"resulting_condition" : "1 and multiple equal(t1.a, t2.p)" "transformation" : "constant_propagation" ,"resulting_condition" : "1 and multiple equal(t1.a, t2.p)" "transformation" : "trivial_condition_removal" ,"resulting_condition" : "multiple equal(t1.a, t2.p)" "ref_optimizer_key_uses" : []"rows_estimation" : ["table" : "t1" ,"table_scan" : {"rows" : 4 ,"read_cost" : 0 .01053322 ,"read_and_compare_cost" : 0 .01066122 "table" : "t2" ,"table_scan" : {"rows" : 101 ,"read_cost" : 0 .022600505 ,"read_and_compare_cost" : 0 .025832505 "semijoin_table_pullout" : {"pulled_out_tables" : []"table_dependencies" : ["table" : "t1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"table" : "t2" ,"row_may_be_null" : false,"map_bit" : 1 ,"depends_on_map_bits" : []"execution_plan_for_potential_materialization" : {"steps" : ["considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t2" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 101 ,"rows_after_filter" : 101 ,"rows_out" : 101 ,"cost" : 0 .025832505 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 101 ,"rows_out" : 101 ,"cost" : 0 .025832505 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t2" ,"rows_for_plan" : 101 ,"cost_for_plan" : 0 .025832505 "considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 4 ,"rows_after_filter" : 4 ,"rows_out" : 4 ,"cost" : 0 .01066122 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 4 ,"rows_out" : 4 ,"cost" : 0 .01066122 ,"uses_join_buffering" : false"best_access_path" : {"table" : "t2" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 101 ,"rows_after_filter" : 101 ,"rows_out" : 101 ,"cost" : 0 .025832505 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 101 ,"rows_out" : 101 ,"cost" : 0 .025832505 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 4 ,"cost_for_plan" : 0 .01066122 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t1" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t2" ,"plan_details" : {"record_count" : 4 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 101 ,"rows_after_filter" : 101 ,"rows_out" : 101 ,"cost" : 0 .063593833 ,"cost_without_join_buffer" : 0 .10333002 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 101 ,"rows_out" : 101 ,"cost" : 0 .063593833 ,"uses_join_buffering" : true"plan_prefix" : "t1" ,"table" : "t2" ,"rows_for_plan" : 404 ,"cost_for_plan" : 0 .074255053 ,"semijoin_strategy_choice" : ["strategy" : "FirstMatch" ,"rows" : 4 ,"cost" : 0 .074255053 "strategy" : "SJ-Materialization" ,"rows" : 4 ,"cost" : 0 .078768645 "strategy" : "DuplicateWeedout" ,"prefix_row_count" : 4 ,"tmp_table_rows" : 1 ,"sj_inner_fanout" : 101 ,"rows" : 4 ,"dups_cost" : 0 .074255053 ,"write_cost" : 0 .02564388 ,"full_lookup_cost" : 0 .06503188 ,"total_cost" : 0 .164930813 "chosen_strategy" : "FirstMatch" "sj_rows_out" : 1 ,"sj_rows_for_plan" : 4 ,"sj_filtered" : 0 .99009901 "plan_prefix" : "" ,"table" : "t2" ,"rows_for_plan" : 101 ,"cost_for_plan" : 0 .025832505 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"fix_semijoin_strategies_for_picked_join_order" : ["semi_join_strategy" : "FirstMatch" ,"join_order" : ["table" : "t2" "best_join_order" : ["t1" , "t2" ],"rows" : 4 ,"cost" : 0 .074255053 "substitute_best_equal" : {"condition" : "WHERE" ,"resulting_condition" : "t2.p = t1.a" "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "t1" ,"attached_condition" : null"table" : "t2" ,"attached_condition" : "t2.p = t1.a" "make_join_readinfo" : []0 0 ; and indexes.; ; ; set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; set @save_use_stat_tables= @@use_stat_tables; set @@optimizer_use_condition_selectivity=4 ; set @@use_stat_tables= PREFERABLY; set optimizer_trace='enabled=on' ; 2 and a=5 and b=1 ; 1 SIMPLE t1 ref pk,pk_a,pk_a_b pk_a_b 15 const,const,const 1 Using index; 2 and a=5 and b=1 {"steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["expanded_query" : "select t1.pk AS pk,t1.a AS a,t1.b AS b from t1 where t1.pk = 2 and t1.a = 5 and t1.b = 1" "join_optimization" : {"select_id" : 1 ,"steps" : ["condition_processing" : {"condition" : "WHERE" ,"original_condition" : "t1.pk = 2 and t1.a = 5 and t1.b = 1" ,"steps" : ["transformation" : "equality_propagation" ,"resulting_condition" : "multiple equal(2, t1.pk) and multiple equal(5, t1.a) and multiple equal(1, t1.b)" "transformation" : "constant_propagation" ,"resulting_condition" : "multiple equal(2, t1.pk) and multiple equal(5, t1.a) and multiple equal(1, t1.b)" "transformation" : "trivial_condition_removal" ,"resulting_condition" : "multiple equal(2, t1.pk) and multiple equal(5, t1.a) and multiple equal(1, t1.b)" "ref_optimizer_key_uses" : ["table" : "t1" ,"index" : "pk" ,"field" : "pk" ,"equals" : "2" ,"null_rejecting" : true"table" : "t1" ,"index" : "pk_a" ,"field" : "pk" ,"equals" : "2" ,"null_rejecting" : true"table" : "t1" ,"index" : "pk_a" ,"field" : "a" ,"equals" : "5" ,"null_rejecting" : true"table" : "t1" ,"index" : "pk_a_b" ,"field" : "pk" ,"equals" : "2" ,"null_rejecting" : true"table" : "t1" ,"index" : "pk_a_b" ,"field" : "a" ,"equals" : "5" ,"null_rejecting" : true"table" : "t1" ,"index" : "pk_a_b" ,"field" : "b" ,"equals" : "1" ,"null_rejecting" : true"rows_estimation" : ["table" : "t1" ,"range_analysis" : {"table_scan" : {"rows" : 10 ,"cost" : 0 .01159965 "potential_range_indexes" : ["index" : "pk" ,"usable" : true,"key_parts" : ["pk" ]"index" : "pk_a" ,"usable" : true,"key_parts" : ["pk" , "a" ]"index" : "pk_a_b" ,"usable" : true,"key_parts" : ["pk" , "a" , "b" ]"best_covering_index_scan" : {"index" : "pk_a_b" ,"cost" : 0 .007173242 ,"chosen" : true"setup_range_conditions" : [],"analyzing_range_alternatives" : {"range_scan_alternatives" : ["index" : "pk" ,"ranges" : ["(2) <= (pk) <= (2)" ],"rowid_ordered" : true,"using_mrr" : false,"index_only" : false,"rows" : 1 ,"cost" : 0 .002574553 ,"chosen" : true"index" : "pk_a" ,"ranges" : ["(2,5) <= (pk,a) <= (2,5)" ],"rowid_ordered" : true,"using_mrr" : false,"index_only" : false,"rows" : 1 ,"cost" : 0 .002574553 ,"chosen" : false,"cause" : "cost" "index" : "pk_a_b" ,"ranges" : ["(2,5,1) <= (pk,a,b) <= (2,5,1)" ],"rowid_ordered" : true,"using_mrr" : false,"index_only" : true,"rows" : 1 ,"cost" : 0 .001478954 ,"chosen" : true"analyzing_roworder_intersect" : {"intersecting_indexes" : ["index" : "pk" ,"index_scan_cost" : 0 .000806227 ,"cumulated_index_scan_cost" : 0 .000806227 ,"disk_sweep_cost" : 0 .001143284 ,"cumulative_total_cost" : 0 .001949511 ,"usable" : true,"matching_rows_now" : 1 ,"intersect_covering_with_this_index" : false,"chosen" : true"index" : "pk_a" ,"usable" : false,"cause" : "does not reduce cost of intersect" "index" : "pk_a_b" ,"usable" : false,"cause" : "does not reduce cost of intersect" "clustered_pk" : {"clustered_pk_added_to_intersect" : false,"cause" : "no clustered pk index" "chosen" : false,"cause" : "cost" "group_index_range" : {"chosen" : false,"cause" : "no group by or distinct" "chosen_range_access_summary" : {"range_access_plan" : {"type" : "range_scan" ,"index" : "pk_a_b" ,"rows" : 1 ,"ranges" : ["(2,5,1) <= (pk,a,b) <= (2,5,1)" ]"rows_for_plan" : 1 ,"cost_for_plan" : 0 .001478954 ,"chosen" : true"table" : "t1" ,"rowid_filters" : ["key" : "pk" ,"build_cost" : 0 .000899465 ,"rows" : 1 "key" : "pk_a" ,"build_cost" : 0 .000899465 ,"rows" : 1 "key" : "pk_a_b" ,"build_cost" : 0 .000899465 ,"rows" : 1 "selectivity_for_indexes" : ["index_name" : "pk_a_b" ,"selectivity_from_index" : 0 .1 "selectivity_for_columns" : ["column_name" : "a" ,"ranges" : ["5 <= a <= 5" ],"selectivity_from_histogram" : 0 .1 "column_name" : "b" ,"ranges" : ["1 <= b <= 1" ],"selectivity_from_histogram" : 0 .1 "cond_selectivity" : 0 .1 "table_dependencies" : ["table" : "t1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "ref" ,"index" : "pk" ,"used_range_estimates" : true,"rows" : 1 ,"cost" : 0 .002024411 ,"chosen" : true"access_type" : "ref" ,"index" : "pk_a" ,"used_range_estimates" : true,"rows" : 1 ,"cost" : 0 .002024411 ,"chosen" : false,"cause" : "cost" "access_type" : "ref" ,"index" : "pk_a_b" ,"used_range_estimates" : true,"rows" : 1 ,"cost" : 0 .000928812 ,"chosen" : true"type" : "scan" ,"chosen" : false,"cause" : "cost" "chosen_access_method" : {"type" : "ref" ,"index" : "pk_a_b" ,"rows_read" : 1 ,"rows_out" : 1 ,"cost" : 0 .000928812 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 1 ,"cost_for_plan" : 0 .000928812 "best_join_order" : ["t1" ],"rows" : 1 ,"cost" : 0 .000928812 "substitute_best_equal" : {"condition" : "WHERE" ,"resulting_condition" : "t1.pk = 2 and t1.a = 5 and t1.b = 1" "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "t1" ,"attached_condition" : null"make_join_readinfo" : []0 0 set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set @@use_stat_tables= @save_use_stat_tables; ; set optimizer_trace="enabled=off" ; not traced; 1 ),(2 ),(3 ),(4 ); ; 1 ),(2 ),(3 ),(4 ); 0 ; set a= a+ (select count(*) from t2); return a; 0 ; ; return a; set optimizer_trace='enabled=on' ; ; 4 4 4 4 ; "steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["expanded_query" : "select f1(t1.a) AS `f1(a)` from t1" "join_optimization" : {"select_id" : 1 ,"steps" : ["rows_estimation" : ["table" : "t1" ,"table_scan" : {"rows" : 4 ,"read_cost" : 0 .01053322 ,"read_and_compare_cost" : 0 .01066122 "table_dependencies" : ["table" : "t1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 4 ,"rows_after_filter" : 4 ,"rows_out" : 4 ,"cost" : 0 .01066122 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 4 ,"rows_out" : 4 ,"cost" : 0 .01066122 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 4 ,"cost_for_plan" : 0 .01066122 "best_join_order" : ["t1" ],"rows" : 4 ,"cost" : 0 .01066122 "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "t1" ,"attached_condition" : null"make_join_readinfo" : []0 0 ; 4 4 4 4 ; "steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["expanded_query" : "select f2(t1.a) AS `f2(a)` from t1" "join_optimization" : {"select_id" : 1 ,"steps" : ["rows_estimation" : ["table" : "t1" ,"table_scan" : {"rows" : 4 ,"read_cost" : 0 .01053322 ,"read_and_compare_cost" : 0 .01066122 "table_dependencies" : ["table" : "t1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 4 ,"rows_after_filter" : 4 ,"rows_out" : 4 ,"cost" : 0 .01066122 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 4 ,"rows_out" : 4 ,"cost" : 0 .01066122 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 4 ,"cost_for_plan" : 0 .01066122 "best_join_order" : ["t1" ],"rows" : 4 ,"cost" : 0 .01066122 "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "t1" ,"attached_condition" : null"make_join_readinfo" : []0 0 ; ; ; set optimizer_trace='enabled=off' ; 18489 : Limit the memory used by the optimizer trace; 1 ),(2 ); set optimizer_trace='enabled=on' ; set @save_optimizer_trace_max_mem_size= @@optimizer_trace_max_mem_size; ; 1 2 ; 2728 set optimizer_trace_max_mem_size=100 ; ; 1 2 ; "steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : [2628 0 set optimizer_trace_max_mem_size=0 ; ; 1 2 ; 2728 0 ; set optimizer_trace='enabled=off' ; set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size; 18527 : Optimizer trace for DELETE query shows table:null; 0 ),(1 ),(2 ),(3 ),(4 ),(5 ),(6 ),(7 ),(8 ),(9 ); ; ; ; set optimizer_trace=1 ; 3 ; 1 SIMPLE t0 range a a 5 NULL 3 Using where; 3 {"steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["expanded_query" : "delete from t0 using dual where t0.a < 3" "table" : "t0" ,"range_analysis" : {"table_scan" : {"rows" : 10 ,"cost" : 0 .01159965 "potential_range_indexes" : ["index" : "a" ,"usable" : true,"key_parts" : ["a" ]"setup_range_conditions" : [],"analyzing_range_alternatives" : {"range_scan_alternatives" : ["index" : "a" ,"ranges" : ["(NULL) < (a) < (3)" ],"rowid_ordered" : false,"using_mrr" : false,"index_only" : false,"rows" : 3 ,"cost" : 0 .005042291 ,"chosen" : true"group_index_range" : {"chosen" : false,"cause" : "no group by or distinct" "chosen_range_access_summary" : {"range_access_plan" : {"type" : "range_scan" ,"index" : "a" ,"rows" : 3 ,"ranges" : ["(NULL) < (a) < (3)" ]"rows_for_plan" : 3 ,"cost_for_plan" : 0 .005042291 ,"chosen" : true0 0 ; set optimizer_trace='enabled=off' ; 18528 : Optimizer trace support for multi-table UPDATE and DELETEset optimizer_trace=1 ; ; 0 ),(1 ),(2 ),(3 ),(4 ),(5 ),(6 ),(7 ),(8 ),(9 ); ; ; ; ; ; and t1.a<3 ; 1 SIMPLE t0 range a a 5 NULL 3 Using where1 SIMPLE t1 ref a a 5 test.t0.a 1 ; and t1.a<3 {"steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["expanded_query" : "delete from t0,t1 using t0 join t1 where t0.a = t1.a and t1.a < 3" "join_optimization" : {"select_id" : 1 ,"steps" : ["condition_processing" : {"condition" : "WHERE" ,"original_condition" : "t0.a = t1.a and t1.a < 3" ,"steps" : ["transformation" : "equality_propagation" ,"resulting_condition" : "t1.a < 3 and multiple equal(t0.a, t1.a)" "transformation" : "constant_propagation" ,"resulting_condition" : "t1.a < 3 and multiple equal(t0.a, t1.a)" "transformation" : "trivial_condition_removal" ,"resulting_condition" : "t1.a < 3 and multiple equal(t0.a, t1.a)" "ref_optimizer_key_uses" : ["table" : "t0" ,"index" : "a" ,"field" : "a" ,"equals" : "t1.a" ,"null_rejecting" : true"table" : "t1" ,"index" : "a" ,"field" : "a" ,"equals" : "t0.a" ,"null_rejecting" : true"rows_estimation" : ["table" : "t0" ,"range_analysis" : {"table_scan" : {"rows" : 10 ,"cost" : 0 .01159965 "potential_range_indexes" : ["index" : "a" ,"usable" : true,"key_parts" : ["a" ]"best_covering_index_scan" : {"index" : "a" ,"cost" : 0 .007173242 ,"chosen" : true"setup_range_conditions" : [],"analyzing_range_alternatives" : {"range_scan_alternatives" : ["index" : "a" ,"ranges" : ["(NULL) < (a) < (3)" ],"rowid_ordered" : false,"using_mrr" : false,"index_only" : true,"rows" : 3 ,"cost" : 0 .001755494 ,"chosen" : true"analyzing_roworder_intersect" : {"cause" : "too few roworder scans" "group_index_range" : {"chosen" : false,"cause" : "not single_table" "chosen_range_access_summary" : {"range_access_plan" : {"type" : "range_scan" ,"index" : "a" ,"rows" : 3 ,"ranges" : ["(NULL) < (a) < (3)" ]"rows_for_plan" : 3 ,"cost_for_plan" : 0 .001755494 ,"chosen" : true"table" : "t0" ,"rowid_filters" : ["key" : "a" ,"build_cost" : 0 .001129926 ,"rows" : 3 "selectivity_for_indexes" : ["index_name" : "a" ,"selectivity_from_index" : 0 .3 "selectivity_for_columns" : [],"cond_selectivity" : 0 .3 "table" : "t1" ,"range_analysis" : {"table_scan" : {"rows" : 10 ,"cost" : 0 .01159965 "potential_range_indexes" : ["index" : "a" ,"usable" : true,"key_parts" : ["a" ]"best_covering_index_scan" : {"index" : "a" ,"cost" : 0 .007173242 ,"chosen" : true"setup_range_conditions" : [],"analyzing_range_alternatives" : {"range_scan_alternatives" : ["index" : "a" ,"ranges" : ["(NULL) < (a) < (3)" ],"rowid_ordered" : false,"using_mrr" : false,"index_only" : true,"rows" : 3 ,"cost" : 0 .001755494 ,"chosen" : true"analyzing_roworder_intersect" : {"cause" : "too few roworder scans" "group_index_range" : {"chosen" : false,"cause" : "not single_table" "chosen_range_access_summary" : {"range_access_plan" : {"type" : "range_scan" ,"index" : "a" ,"rows" : 3 ,"ranges" : ["(NULL) < (a) < (3)" ]"rows_for_plan" : 3 ,"cost_for_plan" : 0 .001755494 ,"chosen" : true"table" : "t1" ,"rowid_filters" : ["key" : "a" ,"build_cost" : 0 .001129926 ,"rows" : 3 "selectivity_for_indexes" : ["index_name" : "a" ,"selectivity_from_index" : 0 .3 "selectivity_for_columns" : [],"cond_selectivity" : 0 .3 "table_dependencies" : ["table" : "t0" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"table" : "t1" ,"row_may_be_null" : false,"map_bit" : 1 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t0" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "range" ,"range_index" : "a" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .001755494 ,"chosen" : true"chosen_access_method" : {"type" : "range" ,"index" : "a" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .001755494 ,"uses_join_buffering" : false"best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "range" ,"range_index" : "a" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .001755494 ,"chosen" : true"chosen_access_method" : {"type" : "range" ,"index" : "a" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .001755494 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t0" ,"rows_for_plan" : 3 ,"cost_for_plan" : 0 .001755494 ,"rest_of_plan" : ["plan_prefix" : "t0" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "ref" ,"index" : "a" ,"used_range_estimates" : false,"reason" : "not better than ref estimates" ,"rows" : 1 ,"cost" : 0 .002376836 ,"chosen" : true"type" : "scan" ,"chosen" : false,"cause" : "cost" "chosen_access_method" : {"type" : "ref" ,"index" : "a" ,"rows_read" : 1 ,"rows_out" : 1 ,"cost" : 0 .002376836 ,"uses_join_buffering" : false"plan_prefix" : "t0" ,"table" : "t1" ,"rows_for_plan" : 3 ,"cost_for_plan" : 0 .00413233 "plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 3 ,"cost_for_plan" : 0 .001755494 ,"rest_of_plan" : ["plan_prefix" : "t1" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t0" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "ref" ,"index" : "a" ,"rec_per_key_stats_missing" : true,"used_range_estimates" : false,"reason" : "not better than ref estimates" ,"rows" : 1 .166666667 ,"cost" : 0 .002392836 ,"chosen" : true"type" : "scan" ,"chosen" : false,"cause" : "cost" "chosen_access_method" : {"type" : "ref" ,"index" : "a" ,"rows_read" : 1 .166666667 ,"rows_out" : 1 .166666667 ,"cost" : 0 .002392836 ,"uses_join_buffering" : false"plan_prefix" : "t1" ,"table" : "t0" ,"rows_for_plan" : 3 .5 ,"cost_for_plan" : 0 .00414833 ,"pruned_by_cost" : true,"current_cost" : 0 .00414833 ,"best_cost" : 0 .00413233 "best_join_order" : ["t0" , "t1" ],"rows" : 3 ,"cost" : 0 .00413233 "substitute_best_equal" : {"condition" : "WHERE" ,"resulting_condition" : "t1.a = t0.a and t0.a < 3" "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "t0" ,"attached_condition" : "t0.a < 3 and t0.a is not null" "table" : "t1" ,"attached_condition" : null"make_join_readinfo" : []0 0 ; set optimizer_trace='enabled=off' ; set optimizer_trace=1 ; ; 1 ),(2 ),(3 ); ; 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 2 DERIVED t1 ALL NULL NULL NULL NULL 3 ; "steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["derived" : {"table" : "q" ,"select_id" : 2 ,"algorithm" : "materialized" "join_preparation" : {"select_id" : 2 ,"steps" : ["expanded_query" : "/* select#2 */ select rand() AS `rand()` from t1" "expanded_query" : "/* select#1 */ select q.`rand()` AS `rand()` from (/* select#2 */ select rand() AS `rand()` from t1) q" "join_optimization" : {"select_id" : 1 ,"steps" : ["join_optimization" : {"select_id" : 2 ,"steps" : ["rows_estimation" : ["table" : "t1" ,"table_scan" : {"rows" : 3 ,"read_cost" : 0 .010408815 ,"read_and_compare_cost" : 0 .010504815 "table_dependencies" : ["table" : "t1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 3 ,"cost_for_plan" : 0 .010504815 "best_join_order" : ["t1" ],"rows" : 3 ,"cost" : 0 .010504815 "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "t1" ,"attached_condition" : null"make_join_readinfo" : []"rows_estimation" : ["table" : "<derived2>" ,"table_scan" : {"rows" : 3 ,"read_cost" : 0 .010031052 ,"read_and_compare_cost" : 0 .010134054 "table_dependencies" : ["table" : "<derived2>" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "<derived2>" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .010134054 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .010134054 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "<derived2>" ,"rows_for_plan" : 3 ,"cost_for_plan" : 0 .010134054 "best_join_order" : ["<derived2>" ],"rows" : 3 ,"cost" : 0 .010134054 "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "<derived2>" ,"attached_condition" : null"make_join_readinfo" : []0 0 ; set optimizer_trace='enabled=off' ; set optimizer_trace=1 ; ; 1 ),(2 ),(3 ); ; 1 ),(2 ),(3 ),(1 ),(2 ),(3 ),(1 ),(2 ),(3 ); set @save_optimizer_switch= @@optimizer_switch; ; 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t_inner_1 ALL NULL NULL NULL NULL 3 2 MATERIALIZED t_inner_2 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join); "steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["join_preparation" : {"select_id" : 2 ,"steps" : ["transformation" : {"select_id" : 2 ,"from" : "IN (SELECT)" ,"to" : "semijoin" ,"chosen" : true"expanded_query" : "/* select#2 */ select t_inner_1.a from t1 t_inner_1 join t1 t_inner_2" "expanded_query" : "/* select#1 */ select t1.a AS a from t1 where t1.a in (/* select#2 */ select t_inner_1.a from t1 t_inner_1 join t1 t_inner_2)" "join_optimization" : {"select_id" : 1 ,"steps" : ["transformation" : {"select_id" : 2 ,"from" : "IN (SELECT)" ,"to" : "materialization" ,"sjm_scan_allowed" : true,"possible" : true"transformation" : {"select_id" : 2 ,"from" : "IN (SELECT)" ,"to" : "semijoin" ,"converted_to_semi_join" : true"condition_processing" : {"condition" : "WHERE" ,"original_condition" : "1 and t1.a = t_inner_1.a" ,"steps" : ["transformation" : "equality_propagation" ,"resulting_condition" : "1 and multiple equal(t1.a, t_inner_1.a)" "transformation" : "constant_propagation" ,"resulting_condition" : "1 and multiple equal(t1.a, t_inner_1.a)" "transformation" : "trivial_condition_removal" ,"resulting_condition" : "multiple equal(t1.a, t_inner_1.a)" "ref_optimizer_key_uses" : []"rows_estimation" : ["table" : "t1" ,"table_scan" : {"rows" : 3 ,"read_cost" : 0 .010408815 ,"read_and_compare_cost" : 0 .010504815 "table" : "t_inner_1" ,"table_scan" : {"rows" : 3 ,"read_cost" : 0 .010408815 ,"read_and_compare_cost" : 0 .010504815 "table" : "t_inner_2" ,"table_scan" : {"rows" : 3 ,"read_cost" : 0 .010408815 ,"read_and_compare_cost" : 0 .010504815 "semijoin_table_pullout" : {"pulled_out_tables" : []"table_dependencies" : ["table" : "t1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"table" : "t_inner_1" ,"row_may_be_null" : false,"map_bit" : 1 ,"depends_on_map_bits" : []"table" : "t_inner_2" ,"row_may_be_null" : false,"map_bit" : 2 ,"depends_on_map_bits" : []"execution_plan_for_potential_materialization" : {"steps" : ["considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"uses_join_buffering" : false"best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t_inner_1" ,"rows_for_plan" : 3 ,"cost_for_plan" : 0 .010504815 ,"rest_of_plan" : ["plan_prefix" : "t_inner_1" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .011523207 ,"cost_without_join_buffer" : 0 .031514445 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .011523207 ,"uses_join_buffering" : true"plan_prefix" : "t_inner_1" ,"table" : "t_inner_2" ,"rows_for_plan" : 9 ,"cost_for_plan" : 0 .022028022 "plan_prefix" : "" ,"table" : "t_inner_2" ,"rows_for_plan" : 3 ,"cost_for_plan" : 0 .010504815 ,"pruned_by_heuristic" : true"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"uses_join_buffering" : false"best_access_path" : {"table" : "t_inner_1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"uses_join_buffering" : false"best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 3 ,"cost_for_plan" : 0 .010504815 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t1" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_1" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .011523207 ,"cost_without_join_buffer" : 0 .031514445 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .011523207 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .011523207 ,"cost_without_join_buffer" : 0 .031514445 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .011523207 ,"uses_join_buffering" : true"plan_prefix" : "t1" ,"table" : "t_inner_1" ,"rows_for_plan" : 9 ,"cost_for_plan" : 0 .022028022 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t1,t_inner_1" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 9 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .015203373 ,"cost_without_join_buffer" : 0 .094543335 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .015203373 ,"uses_join_buffering" : true"plan_prefix" : "t1,t_inner_1" ,"table" : "t_inner_2" ,"rows_for_plan" : 27 ,"cost_for_plan" : 0 .037231395 ,"semijoin_strategy_choice" : ["strategy" : "FirstMatch" ,"rows" : 3 ,"cost" : 0 .136562595 "strategy" : "SJ-Materialization" ,"rows" : 3 ,"cost" : 0 .059588485 "strategy" : "DuplicateWeedout" ,"prefix_row_count" : 3 ,"tmp_table_rows" : 1 ,"sj_inner_fanout" : 9 ,"rows" : 3 ,"dups_cost" : 0 .037231395 ,"write_cost" : 0 .02548291 ,"full_lookup_cost" : 0 .00434619 ,"total_cost" : 0 .067060495 "chosen_strategy" : "SJ-Materialization" "sj_rows_out" : 0 .333333333 ,"sj_rows_for_plan" : 3 ,"sj_filtered" : 11 .11111111 "plan_prefix" : "t1" ,"table" : "t_inner_2" ,"rows_for_plan" : 9 ,"cost_for_plan" : 0 .022028022 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "" ,"table" : "t_inner_1" ,"rows_for_plan" : 3 ,"cost_for_plan" : 0 .010504815 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "" ,"table" : "t_inner_2" ,"rows_for_plan" : 3 ,"cost_for_plan" : 0 .010504815 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"fix_semijoin_strategies_for_picked_join_order" : ["semi_join_strategy" : "SJ-Materialization" ,"join_order" : ["table" : "t_inner_1" "table" : "t_inner_2" "best_join_order" : ["t1" , "<subquery2>" ],"rows" : 3 ,"cost" : 0 .059588485 "substitute_best_equal" : {"condition" : "WHERE" ,"resulting_condition" : "1" "attaching_conditions_to_tables" : {"attached_conditions_computation" : ["condition_on_constant_tables" : "1" ,"computing_condition" : []"attached_conditions_summary" : ["table" : "t1" ,"attached_condition" : null"table" : "t_inner_1" ,"attached_condition" : null"table" : "t_inner_2" ,"attached_condition" : null"table" : "<subquery2>" ,"attached_condition" : null"make_join_readinfo" : []0 0 set optimizer_switch='materialization=off' ; m t2 t_inner_2, t1 t_inner_1) and ; 1 PRIMARY t_outer_1 ALL NULL NULL NULL NULL 3 1 PRIMARY t_inner_1 ALL NULL NULL NULL NULL 3 Using where; Start temporary; Using join buffer (flat, BNL join) 1 PRIMARY t_inner_2 ALL NULL NULL NULL NULL 9 End temporary; Using join buffer (incremental, BNL join) 1 PRIMARY t_inner_4 ALL NULL NULL NULL NULL 3 Start temporary; Using join buffer (incremental, BNL join) 1 PRIMARY t_outer_2 ALL NULL NULL NULL NULL 9 Using join buffer (incremental, BNL join)1 PRIMARY t_inner_3 ALL NULL NULL NULL NULL 9 Using where; End temporary; Using join buffer (incremental, BNL join) ; m t2 t_inner_2, t1 t_inner_1) and "steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["join_preparation" : {"select_id" : 2 ,"steps" : ["transformation" : {"select_id" : 2 ,"from" : "IN (SELECT)" ,"to" : "semijoin" ,"chosen" : true"expanded_query" : "/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1" "join_preparation" : {"select_id" : 3 ,"steps" : ["transformation" : {"select_id" : 3 ,"from" : "IN (SELECT)" ,"to" : "semijoin" ,"chosen" : true"expanded_query" : "/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4" "expanded_query" : "/* select#1 */ select t_outer_1.a AS a,t_outer_2.a AS a from t1 t_outer_1 join t2 t_outer_2 where t_outer_1.a in (/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1) and t_outer_2.a in (/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4)" "join_optimization" : {"select_id" : 1 ,"steps" : ["transformation" : {"select_id" : 2 ,"from" : "IN (SELECT)" ,"to" : "materialization" ,"sjm_scan_allowed" : true,"possible" : true"transformation" : {"select_id" : 2 ,"from" : "IN (SELECT)" ,"to" : "semijoin" ,"converted_to_semi_join" : true"transformation" : {"select_id" : 3 ,"from" : "IN (SELECT)" ,"to" : "materialization" ,"sjm_scan_allowed" : true,"possible" : true"transformation" : {"select_id" : 3 ,"from" : "IN (SELECT)" ,"to" : "semijoin" ,"converted_to_semi_join" : true"condition_processing" : {"condition" : "WHERE" ,"original_condition" : "1 and 1 and t_outer_1.a = t_inner_1.a and t_outer_2.a = t_inner_3.a" ,"steps" : ["transformation" : "equality_propagation" ,"resulting_condition" : "1 and 1 and multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)" "transformation" : "constant_propagation" ,"resulting_condition" : "1 and 1 and multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)" "transformation" : "trivial_condition_removal" ,"resulting_condition" : "multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)" "ref_optimizer_key_uses" : []"rows_estimation" : ["table" : "t_outer_1" ,"table_scan" : {"rows" : 3 ,"read_cost" : 0 .010408815 ,"read_and_compare_cost" : 0 .010504815 "table" : "t_outer_2" ,"table_scan" : {"rows" : 9 ,"read_cost" : 0 .011155245 ,"read_and_compare_cost" : 0 .011443245 "table" : "t_inner_2" ,"table_scan" : {"rows" : 9 ,"read_cost" : 0 .011155245 ,"read_and_compare_cost" : 0 .011443245 "table" : "t_inner_1" ,"table_scan" : {"rows" : 3 ,"read_cost" : 0 .010408815 ,"read_and_compare_cost" : 0 .010504815 "table" : "t_inner_3" ,"table_scan" : {"rows" : 9 ,"read_cost" : 0 .011155245 ,"read_and_compare_cost" : 0 .011443245 "table" : "t_inner_4" ,"table_scan" : {"rows" : 3 ,"read_cost" : 0 .010408815 ,"read_and_compare_cost" : 0 .010504815 "semijoin_table_pullout" : {"pulled_out_tables" : []"semijoin_table_pullout" : {"pulled_out_tables" : []"table_dependencies" : ["table" : "t_outer_1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"table" : "t_outer_2" ,"row_may_be_null" : false,"map_bit" : 1 ,"depends_on_map_bits" : []"table" : "t_inner_2" ,"row_may_be_null" : false,"map_bit" : 2 ,"depends_on_map_bits" : []"table" : "t_inner_1" ,"row_may_be_null" : false,"map_bit" : 3 ,"depends_on_map_bits" : []"table" : "t_inner_3" ,"row_may_be_null" : false,"map_bit" : 4 ,"depends_on_map_bits" : []"table" : "t_inner_4" ,"row_may_be_null" : false,"map_bit" : 5 ,"depends_on_map_bits" : []"execution_plan_for_potential_materialization" : {"steps" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_outer_1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"uses_join_buffering" : false"best_access_path" : {"table" : "t_inner_1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"uses_join_buffering" : false"best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .011443245 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .011443245 ,"uses_join_buffering" : false"best_access_path" : {"table" : "t_outer_2" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .011443245 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .011443245 ,"uses_join_buffering" : false"best_access_path" : {"table" : "t_inner_4" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"uses_join_buffering" : false"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .011443245 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .011443245 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t_outer_1" ,"rows_for_plan" : 3 ,"cost_for_plan" : 0 .010504815 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_1" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .011523207 ,"cost_without_join_buffer" : 0 .031514445 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .011523207 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .014133225 ,"cost_without_join_buffer" : 0 .034329735 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .014133225 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_outer_2" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .014133225 ,"cost_without_join_buffer" : 0 .034329735 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .014133225 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_4" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .011523207 ,"cost_without_join_buffer" : 0 .031514445 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .011523207 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .014133225 ,"cost_without_join_buffer" : 0 .034329735 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .014133225 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1" ,"table" : "t_outer_2" ,"rows_for_plan" : 27 ,"cost_for_plan" : 0 .02463804 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_outer_2" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .050443503 ,"cost_without_join_buffer" : 0 .308967615 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .050443503 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_1" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .024600489 ,"cost_without_join_buffer" : 0 .283630005 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .024600489 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_4" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .024600489 ,"cost_without_join_buffer" : 0 .283630005 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .024600489 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .050443503 ,"cost_without_join_buffer" : 0 .308967615 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .050443503 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_outer_2" ,"table" : "t_inner_1" ,"rows_for_plan" : 81 ,"cost_for_plan" : 0 .049238529 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_outer_2,t_inner_1" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 81 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .172815333 ,"cost_without_join_buffer" : 0 .926902845 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .172815333 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_4" ,"plan_details" : {"record_count" : 81 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .067582275 ,"cost_without_join_buffer" : 0 .850890015 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .067582275 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 81 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .172815333 ,"cost_without_join_buffer" : 0 .926902845 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .172815333 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_outer_2,t_inner_1" ,"table" : "t_inner_2" ,"rows_for_plan" : 729 ,"cost_for_plan" : 0 .222053862 ,"semijoin_strategy_choice" : ["strategy" : "FirstMatch" ,"rows" : 27 ,"cost" : 1 .23517089 "strategy" : "DuplicateWeedout" ,"prefix_row_count" : 27 ,"tmp_table_rows" : 1 ,"sj_inner_fanout" : 27 ,"rows" : 27 ,"dups_cost" : 0 .222053862 ,"write_cost" : 0 .02934619 ,"full_lookup_cost" : 0 .11734713 ,"total_cost" : 0 .368747182 "chosen_strategy" : "DuplicateWeedout" "sj_rows_out" : 0 .333333333 ,"sj_rows_for_plan" : 27 ,"sj_filtered" : 3 .703703704 ,"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_outer_2,t_inner_1,t_inner_2" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_4" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .034460781 ,"cost_without_join_buffer" : 0 .283630005 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .034460781 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .080024379 ,"cost_without_join_buffer" : 0 .308967615 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .080024379 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_outer_2,t_inner_1,t_inner_2" ,"table" : "t_inner_4" ,"rows_for_plan" : 81 ,"cost_for_plan" : 0 .403207963 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_outer_2,t_inner_1,t_inner_2,t_inner_4" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 81 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .261557961 ,"cost_without_join_buffer" : 0 .926902845 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .261557961 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_outer_2,t_inner_1,t_inner_2,t_inner_4" ,"table" : "t_inner_3" ,"rows_for_plan" : 729 ,"cost_for_plan" : 0 .664765924 ,"semijoin_strategy_choice" : ["strategy" : "FirstMatch" ,"rows" : 27 ,"cost" : 1 .579280032 "strategy" : "DuplicateWeedout" ,"prefix_row_count" : 27 ,"tmp_table_rows" : 1 ,"sj_inner_fanout" : 27 ,"rows" : 27 ,"dups_cost" : 0 .664765924 ,"write_cost" : 0 .02934619 ,"full_lookup_cost" : 0 .11734713 ,"total_cost" : 0 .811459244 "chosen_strategy" : "DuplicateWeedout" "sj_rows_out" : 0 .333333333 ,"sj_rows_for_plan" : 27 ,"sj_filtered" : 3 .703703704 "plan_prefix" : "t_outer_1,t_outer_2,t_inner_1,t_inner_2" ,"table" : "t_inner_3" ,"rows_for_plan" : 243 ,"cost_for_plan" : 0 .448771561 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1,t_outer_2,t_inner_1" ,"table" : "t_inner_4" ,"rows_for_plan" : 243 ,"cost_for_plan" : 0 .116820804 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_outer_2,t_inner_1,t_inner_4" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 243 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .628673451 ,"cost_without_join_buffer" : 2 .780708535 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .628673451 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 243 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .628673451 ,"cost_without_join_buffer" : 2 .780708535 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .628673451 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_outer_2,t_inner_1,t_inner_4" ,"table" : "t_inner_2" ,"rows_for_plan" : 2187 ,"cost_for_plan" : 0 .745494255 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_outer_2,t_inner_1,t_inner_4,t_inner_2" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 2187 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 6 .764540577 ,"cost_without_join_buffer" : 25 .02637682 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 6 .764540577 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_outer_2,t_inner_1,t_inner_4,t_inner_2" ,"table" : "t_inner_3" ,"rows_for_plan" : 19683 ,"cost_for_plan" : 7 .510034832 ,"semijoin_strategy_choice" : ["strategy" : "FirstMatch" ,"rows" : 27 ,"cost" : 28 .96624341 "strategy" : "DuplicateWeedout" ,"prefix_row_count" : 27 ,"tmp_table_rows" : 1 ,"sj_inner_fanout" : 729 ,"rows" : 27 ,"dups_cost" : 7 .510034832 ,"write_cost" : 0 .02934619 ,"full_lookup_cost" : 3 .16837251 ,"total_cost" : 10 .70775353 "chosen_strategy" : "FirstMatch" "sj_rows_out" : 0 .012345679 ,"sj_rows_for_plan" : 27 ,"sj_filtered" : 0 .137174211 ,"pruned_by_cost" : true,"current_cost" : 28 .96624341 ,"best_cost" : 0 .811459244 "plan_prefix" : "t_outer_1,t_outer_2,t_inner_1,t_inner_4" ,"table" : "t_inner_3" ,"rows_for_plan" : 2187 ,"cost_for_plan" : 0 .745494255 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1,t_outer_2,t_inner_1" ,"table" : "t_inner_3" ,"rows_for_plan" : 729 ,"cost_for_plan" : 0 .222053862 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : "min_read_time" "plan_prefix" : "t_outer_1,t_outer_2" ,"table" : "t_inner_2" ,"rows_for_plan" : 243 ,"cost_for_plan" : 0 .075081543 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1,t_outer_2" ,"table" : "t_inner_4" ,"rows_for_plan" : 81 ,"cost_for_plan" : 0 .049238529 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1,t_outer_2" ,"table" : "t_inner_3" ,"rows_for_plan" : 243 ,"cost_for_plan" : 0 .075081543 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1" ,"table" : "t_inner_1" ,"rows_for_plan" : 9 ,"cost_for_plan" : 0 .022028022 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 9 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .024443331 ,"cost_without_join_buffer" : 0 .102989205 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .024443331 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_outer_2" ,"plan_details" : {"record_count" : 9 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .024443331 ,"cost_without_join_buffer" : 0 .102989205 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .024443331 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_4" ,"plan_details" : {"record_count" : 9 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .015203373 ,"cost_without_join_buffer" : 0 .094543335 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .015203373 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 9 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .024443331 ,"cost_without_join_buffer" : 0 .102989205 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .024443331 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1" ,"table" : "t_outer_2" ,"rows_for_plan" : 81 ,"cost_for_plan" : 0 .046471353 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_outer_2" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 81 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .172815333 ,"cost_without_join_buffer" : 0 .926902845 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .172815333 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_4" ,"plan_details" : {"record_count" : 81 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .067582275 ,"cost_without_join_buffer" : 0 .850890015 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .067582275 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 81 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .172815333 ,"cost_without_join_buffer" : 0 .926902845 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .172815333 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_outer_2" ,"table" : "t_inner_2" ,"rows_for_plan" : 729 ,"cost_for_plan" : 0 .219286686 ,"semijoin_strategy_choice" : ["strategy" : "DuplicateWeedout" ,"prefix_row_count" : 3 ,"tmp_table_rows" : 9 ,"sj_inner_fanout" : 27 ,"rows" : 27 ,"dups_cost" : 0 .219286686 ,"write_cost" : 0 .02934619 ,"full_lookup_cost" : 0 .11734713 ,"total_cost" : 0 .365980006 "chosen_strategy" : "DuplicateWeedout" "sj_rows_out" : 0 .333333333 ,"sj_rows_for_plan" : 27 ,"sj_filtered" : 3 .703703704 ,"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_outer_2,t_inner_2" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_4" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .034460781 ,"cost_without_join_buffer" : 0 .283630005 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .034460781 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .080024379 ,"cost_without_join_buffer" : 0 .308967615 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .080024379 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_outer_2,t_inner_2" ,"table" : "t_inner_4" ,"rows_for_plan" : 81 ,"cost_for_plan" : 0 .400440787 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_outer_2,t_inner_2,t_inner_4" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 81 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .261557961 ,"cost_without_join_buffer" : 0 .926902845 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .261557961 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_outer_2,t_inner_2,t_inner_4" ,"table" : "t_inner_3" ,"rows_for_plan" : 729 ,"cost_for_plan" : 0 .661998748 ,"semijoin_strategy_choice" : ["strategy" : "FirstMatch" ,"rows" : 27 ,"cost" : 1 .576512856 "strategy" : "DuplicateWeedout" ,"prefix_row_count" : 27 ,"tmp_table_rows" : 1 ,"sj_inner_fanout" : 27 ,"rows" : 27 ,"dups_cost" : 0 .661998748 ,"write_cost" : 0 .02934619 ,"full_lookup_cost" : 0 .11734713 ,"total_cost" : 0 .808692068 "chosen_strategy" : "DuplicateWeedout" "sj_rows_out" : 0 .333333333 ,"sj_rows_for_plan" : 27 ,"sj_filtered" : 3 .703703704 "plan_prefix" : "t_outer_1,t_inner_1,t_outer_2,t_inner_2" ,"table" : "t_inner_3" ,"rows_for_plan" : 243 ,"cost_for_plan" : 0 .446004385 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1,t_inner_1,t_outer_2" ,"table" : "t_inner_4" ,"rows_for_plan" : 243 ,"cost_for_plan" : 0 .114053628 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_outer_2,t_inner_4" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 243 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .628673451 ,"cost_without_join_buffer" : 2 .780708535 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .628673451 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 243 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .628673451 ,"cost_without_join_buffer" : 2 .780708535 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .628673451 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_outer_2,t_inner_4" ,"table" : "t_inner_2" ,"rows_for_plan" : 2187 ,"cost_for_plan" : 0 .742727079 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_outer_2,t_inner_4,t_inner_2" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 2187 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 6 .764540577 ,"cost_without_join_buffer" : 25 .02637682 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 6 .764540577 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_outer_2,t_inner_4,t_inner_2" ,"table" : "t_inner_3" ,"rows_for_plan" : 19683 ,"cost_for_plan" : 7 .507267656 ,"semijoin_strategy_choice" : ["strategy" : "DuplicateWeedout" ,"prefix_row_count" : 3 ,"tmp_table_rows" : 9 ,"sj_inner_fanout" : 729 ,"rows" : 27 ,"dups_cost" : 7 .507267656 ,"write_cost" : 0 .02934619 ,"full_lookup_cost" : 3 .16837251 ,"total_cost" : 10 .70498636 "chosen_strategy" : "DuplicateWeedout" "sj_rows_out" : 0 .012345679 ,"sj_rows_for_plan" : 27 ,"sj_filtered" : 0 .137174211 ,"pruned_by_cost" : true,"current_cost" : 10 .70498636 ,"best_cost" : 0 .808692068 "plan_prefix" : "t_outer_1,t_inner_1,t_outer_2,t_inner_4" ,"table" : "t_inner_3" ,"rows_for_plan" : 2187 ,"cost_for_plan" : 0 .742727079 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1,t_inner_1,t_outer_2" ,"table" : "t_inner_3" ,"rows_for_plan" : 729 ,"cost_for_plan" : 0 .219286686 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : "min_read_time" "plan_prefix" : "t_outer_1,t_inner_1" ,"table" : "t_inner_2" ,"rows_for_plan" : 81 ,"cost_for_plan" : 0 .046471353 ,"semijoin_strategy_choice" : ["strategy" : "FirstMatch" ,"rows" : 3 ,"cost" : 0 .145008465 "strategy" : "DuplicateWeedout" ,"prefix_row_count" : 3 ,"tmp_table_rows" : 1 ,"sj_inner_fanout" : 27 ,"rows" : 3 ,"dups_cost" : 0 .046471353 ,"write_cost" : 0 .02548291 ,"full_lookup_cost" : 0 .01303857 ,"total_cost" : 0 .084992833 "chosen_strategy" : "DuplicateWeedout" "sj_rows_out" : 0 .333333333 ,"sj_rows_for_plan" : 3 ,"sj_filtered" : 3 .703703704 ,"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_inner_2" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_outer_2" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .017419989 ,"cost_without_join_buffer" : 0 .034329735 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .017419989 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_4" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .012618795 ,"cost_without_join_buffer" : 0 .031514445 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .012618795 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .017419989 ,"cost_without_join_buffer" : 0 .034329735 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .017419989 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_inner_2" ,"table" : "t_outer_2" ,"rows_for_plan" : 27 ,"cost_for_plan" : 0 .102412822 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_inner_2,t_outer_2" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_4" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .034460781 ,"cost_without_join_buffer" : 0 .283630005 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .034460781 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .080024379 ,"cost_without_join_buffer" : 0 .308967615 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .080024379 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_inner_2,t_outer_2" ,"table" : "t_inner_4" ,"rows_for_plan" : 81 ,"cost_for_plan" : 0 .136873603 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_inner_2,t_outer_2,t_inner_4" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 81 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .261557961 ,"cost_without_join_buffer" : 0 .926902845 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .261557961 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_inner_2,t_outer_2,t_inner_4" ,"table" : "t_inner_3" ,"rows_for_plan" : 729 ,"cost_for_plan" : 0 .398431564 ,"semijoin_strategy_choice" : ["strategy" : "FirstMatch" ,"rows" : 27 ,"cost" : 1 .312945672 "strategy" : "DuplicateWeedout" ,"prefix_row_count" : 27 ,"tmp_table_rows" : 1 ,"sj_inner_fanout" : 27 ,"rows" : 27 ,"dups_cost" : 0 .398431564 ,"write_cost" : 0 .02934619 ,"full_lookup_cost" : 0 .11734713 ,"total_cost" : 0 .545124884 "chosen_strategy" : "DuplicateWeedout" "sj_rows_out" : 0 .333333333 ,"sj_rows_for_plan" : 27 ,"sj_filtered" : 3 .703703704 "plan_prefix" : "t_outer_1,t_inner_1,t_inner_2,t_outer_2" ,"table" : "t_inner_3" ,"rows_for_plan" : 243 ,"cost_for_plan" : 0 .182437201 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1,t_inner_1,t_inner_2" ,"table" : "t_inner_4" ,"rows_for_plan" : 9 ,"cost_for_plan" : 0 .097611628 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_inner_2,t_inner_4" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_outer_2" ,"plan_details" : {"record_count" : 9 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .034303623 ,"cost_without_join_buffer" : 0 .102989205 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .034303623 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 9 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .034303623 ,"cost_without_join_buffer" : 0 .102989205 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .034303623 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_inner_2,t_inner_4" ,"table" : "t_outer_2" ,"rows_for_plan" : 81 ,"cost_for_plan" : 0 .131915251 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_inner_2,t_inner_4,t_outer_2" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 81 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .261557961 ,"cost_without_join_buffer" : 0 .926902845 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .261557961 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_inner_2,t_inner_4,t_outer_2" ,"table" : "t_inner_3" ,"rows_for_plan" : 729 ,"cost_for_plan" : 0 .393473212 ,"semijoin_strategy_choice" : ["strategy" : "DuplicateWeedout" ,"prefix_row_count" : 3 ,"tmp_table_rows" : 9 ,"sj_inner_fanout" : 27 ,"rows" : 27 ,"dups_cost" : 0 .393473212 ,"write_cost" : 0 .02934619 ,"full_lookup_cost" : 0 .11734713 ,"total_cost" : 0 .540166532 "chosen_strategy" : "DuplicateWeedout" "sj_rows_out" : 0 .333333333 ,"sj_rows_for_plan" : 27 ,"sj_filtered" : 3 .703703704 "plan_prefix" : "t_outer_1,t_inner_1,t_inner_2,t_inner_4" ,"table" : "t_inner_3" ,"rows_for_plan" : 81 ,"cost_for_plan" : 0 .131915251 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1,t_inner_1,t_inner_2" ,"table" : "t_inner_3" ,"rows_for_plan" : 27 ,"cost_for_plan" : 0 .102412822 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1,t_inner_1" ,"table" : "t_inner_4" ,"rows_for_plan" : 27 ,"cost_for_plan" : 0 .037231395 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_inner_4" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_outer_2" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .065233941 ,"cost_without_join_buffer" : 0 .308967615 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .065233941 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .065233941 ,"cost_without_join_buffer" : 0 .308967615 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .065233941 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .065233941 ,"cost_without_join_buffer" : 0 .308967615 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .065233941 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_inner_4" ,"table" : "t_outer_2" ,"rows_for_plan" : 243 ,"cost_for_plan" : 0 .102465336 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_inner_4,t_outer_2" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 243 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .628673451 ,"cost_without_join_buffer" : 2 .780708535 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .628673451 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 243 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .628673451 ,"cost_without_join_buffer" : 2 .780708535 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .628673451 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_inner_4,t_outer_2" ,"table" : "t_inner_2" ,"rows_for_plan" : 2187 ,"cost_for_plan" : 0 .731138787 ,"semijoin_strategy_choice" : [],"pruned_by_cost" : true,"current_cost" : 0 .731138787 ,"best_cost" : 0 .540166532 "plan_prefix" : "t_outer_1,t_inner_1,t_inner_4,t_outer_2" ,"table" : "t_inner_3" ,"rows_for_plan" : 2187 ,"cost_for_plan" : 0 .731138787 ,"semijoin_strategy_choice" : [],"pruned_by_cost" : true,"current_cost" : 0 .731138787 ,"best_cost" : 0 .540166532 "plan_prefix" : "t_outer_1,t_inner_1,t_inner_4" ,"table" : "t_inner_2" ,"rows_for_plan" : 243 ,"cost_for_plan" : 0 .102465336 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1,t_inner_1,t_inner_4" ,"table" : "t_inner_3" ,"rows_for_plan" : 243 ,"cost_for_plan" : 0 .102465336 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1,t_inner_1" ,"table" : "t_inner_3" ,"rows_for_plan" : 81 ,"cost_for_plan" : 0 .046471353 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1" ,"table" : "t_inner_2" ,"rows_for_plan" : 27 ,"cost_for_plan" : 0 .02463804 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1" ,"table" : "t_inner_4" ,"rows_for_plan" : 9 ,"cost_for_plan" : 0 .022028022 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1" ,"table" : "t_inner_3" ,"rows_for_plan" : 27 ,"cost_for_plan" : 0 .02463804 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "" ,"table" : "t_outer_2" ,"rows_for_plan" : 9 ,"cost_for_plan" : 0 .011443245 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "" ,"table" : "t_inner_1" ,"rows_for_plan" : 3 ,"cost_for_plan" : 0 .010504815 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "" ,"table" : "t_inner_2" ,"rows_for_plan" : 9 ,"cost_for_plan" : 0 .011443245 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "" ,"table" : "t_inner_4" ,"rows_for_plan" : 3 ,"cost_for_plan" : 0 .010504815 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "" ,"table" : "t_inner_3" ,"rows_for_plan" : 9 ,"cost_for_plan" : 0 .011443245 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"fix_semijoin_strategies_for_picked_join_order" : ["semi_join_strategy" : "DuplicateWeedout" "semi_join_strategy" : "DuplicateWeedout" "best_join_order" : ["t_outer_1" ,"t_inner_1" ,"t_inner_2" ,"t_inner_4" ,"t_outer_2" ,"t_inner_3" "rows" : 27 ,"cost" : 0 .540166532 "substitute_best_equal" : {"condition" : "WHERE" ,"resulting_condition" : "t_inner_1.a = t_outer_1.a and t_inner_3.a = t_outer_2.a" "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "t_outer_1" ,"attached_condition" : null"table" : "t_inner_1" ,"attached_condition" : "t_inner_1.a = t_outer_1.a" "table" : "t_inner_2" ,"attached_condition" : null"table" : "t_inner_4" ,"attached_condition" : null"table" : "t_outer_2" ,"attached_condition" : null"table" : "t_inner_3" ,"attached_condition" : "t_inner_3.a = t_outer_2.a" "make_join_readinfo" : []0 0 set optimizer_switch='materialization=on' ; m t2 t_inner_2, t1 t_inner_1) and ; 1 PRIMARY t_outer_1 ALL NULL NULL NULL NULL 3 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t_outer_2 ALL NULL NULL NULL NULL 9 Using join buffer (flat, BNL join)1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t_inner_1 ALL NULL NULL NULL NULL 3 2 MATERIALIZED t_inner_2 ALL NULL NULL NULL NULL 9 Using join buffer (flat, BNL join)3 MATERIALIZED t_inner_4 ALL NULL NULL NULL NULL 3 3 MATERIALIZED t_inner_3 ALL NULL NULL NULL NULL 9 Using join buffer (flat, BNL join); m t2 t_inner_2, t1 t_inner_1) and "steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["join_preparation" : {"select_id" : 2 ,"steps" : ["transformation" : {"select_id" : 2 ,"from" : "IN (SELECT)" ,"to" : "semijoin" ,"chosen" : true"expanded_query" : "/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1" "join_preparation" : {"select_id" : 3 ,"steps" : ["transformation" : {"select_id" : 3 ,"from" : "IN (SELECT)" ,"to" : "semijoin" ,"chosen" : true"expanded_query" : "/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4" "expanded_query" : "/* select#1 */ select t_outer_1.a AS a,t_outer_2.a AS a from t1 t_outer_1 join t2 t_outer_2 where t_outer_1.a in (/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1) and t_outer_2.a in (/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4)" "join_optimization" : {"select_id" : 1 ,"steps" : ["transformation" : {"select_id" : 2 ,"from" : "IN (SELECT)" ,"to" : "materialization" ,"sjm_scan_allowed" : true,"possible" : true"transformation" : {"select_id" : 2 ,"from" : "IN (SELECT)" ,"to" : "semijoin" ,"converted_to_semi_join" : true"transformation" : {"select_id" : 3 ,"from" : "IN (SELECT)" ,"to" : "materialization" ,"sjm_scan_allowed" : true,"possible" : true"transformation" : {"select_id" : 3 ,"from" : "IN (SELECT)" ,"to" : "semijoin" ,"converted_to_semi_join" : true"condition_processing" : {"condition" : "WHERE" ,"original_condition" : "1 and 1 and t_outer_1.a = t_inner_1.a and t_outer_2.a = t_inner_3.a" ,"steps" : ["transformation" : "equality_propagation" ,"resulting_condition" : "1 and 1 and multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)" "transformation" : "constant_propagation" ,"resulting_condition" : "1 and 1 and multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)" "transformation" : "trivial_condition_removal" ,"resulting_condition" : "multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)" "ref_optimizer_key_uses" : []"rows_estimation" : ["table" : "t_outer_1" ,"table_scan" : {"rows" : 3 ,"read_cost" : 0 .010408815 ,"read_and_compare_cost" : 0 .010504815 "table" : "t_outer_2" ,"table_scan" : {"rows" : 9 ,"read_cost" : 0 .011155245 ,"read_and_compare_cost" : 0 .011443245 "table" : "t_inner_2" ,"table_scan" : {"rows" : 9 ,"read_cost" : 0 .011155245 ,"read_and_compare_cost" : 0 .011443245 "table" : "t_inner_1" ,"table_scan" : {"rows" : 3 ,"read_cost" : 0 .010408815 ,"read_and_compare_cost" : 0 .010504815 "table" : "t_inner_3" ,"table_scan" : {"rows" : 9 ,"read_cost" : 0 .011155245 ,"read_and_compare_cost" : 0 .011443245 "table" : "t_inner_4" ,"table_scan" : {"rows" : 3 ,"read_cost" : 0 .010408815 ,"read_and_compare_cost" : 0 .010504815 "semijoin_table_pullout" : {"pulled_out_tables" : []"semijoin_table_pullout" : {"pulled_out_tables" : []"table_dependencies" : ["table" : "t_outer_1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"table" : "t_outer_2" ,"row_may_be_null" : false,"map_bit" : 1 ,"depends_on_map_bits" : []"table" : "t_inner_2" ,"row_may_be_null" : false,"map_bit" : 2 ,"depends_on_map_bits" : []"table" : "t_inner_1" ,"row_may_be_null" : false,"map_bit" : 3 ,"depends_on_map_bits" : []"table" : "t_inner_3" ,"row_may_be_null" : false,"map_bit" : 4 ,"depends_on_map_bits" : []"table" : "t_inner_4" ,"row_may_be_null" : false,"map_bit" : 5 ,"depends_on_map_bits" : []"execution_plan_for_potential_materialization" : {"steps" : ["considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"uses_join_buffering" : false"best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .011443245 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .011443245 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t_inner_1" ,"rows_for_plan" : 3 ,"cost_for_plan" : 0 .010504815 ,"rest_of_plan" : ["plan_prefix" : "t_inner_1" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .014133225 ,"cost_without_join_buffer" : 0 .034329735 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .014133225 ,"uses_join_buffering" : true"plan_prefix" : "t_inner_1" ,"table" : "t_inner_2" ,"rows_for_plan" : 27 ,"cost_for_plan" : 0 .02463804 "plan_prefix" : "" ,"table" : "t_inner_2" ,"rows_for_plan" : 9 ,"cost_for_plan" : 0 .011443245 ,"pruned_by_heuristic" : true"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_4" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"uses_join_buffering" : false"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .011443245 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .011443245 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t_inner_4" ,"rows_for_plan" : 3 ,"cost_for_plan" : 0 .010504815 ,"rest_of_plan" : ["plan_prefix" : "t_inner_4" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .014133225 ,"cost_without_join_buffer" : 0 .034329735 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .014133225 ,"uses_join_buffering" : true"plan_prefix" : "t_inner_4" ,"table" : "t_inner_3" ,"rows_for_plan" : 27 ,"cost_for_plan" : 0 .02463804 "plan_prefix" : "" ,"table" : "t_inner_3" ,"rows_for_plan" : 9 ,"cost_for_plan" : 0 .011443245 ,"pruned_by_heuristic" : true"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_outer_1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"uses_join_buffering" : false"best_access_path" : {"table" : "t_inner_1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"uses_join_buffering" : false"best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .011443245 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .011443245 ,"uses_join_buffering" : false"best_access_path" : {"table" : "t_outer_2" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .011443245 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .011443245 ,"uses_join_buffering" : false"best_access_path" : {"table" : "t_inner_4" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"uses_join_buffering" : false"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .011443245 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .011443245 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t_outer_1" ,"rows_for_plan" : 3 ,"cost_for_plan" : 0 .010504815 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_1" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .011523207 ,"cost_without_join_buffer" : 0 .031514445 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .011523207 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .014133225 ,"cost_without_join_buffer" : 0 .034329735 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .014133225 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_outer_2" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .014133225 ,"cost_without_join_buffer" : 0 .034329735 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .014133225 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_4" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .011523207 ,"cost_without_join_buffer" : 0 .031514445 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .011523207 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .014133225 ,"cost_without_join_buffer" : 0 .034329735 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .014133225 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1" ,"table" : "t_outer_2" ,"rows_for_plan" : 27 ,"cost_for_plan" : 0 .02463804 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_outer_2" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .050443503 ,"cost_without_join_buffer" : 0 .308967615 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .050443503 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_1" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .024600489 ,"cost_without_join_buffer" : 0 .283630005 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .024600489 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_4" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .024600489 ,"cost_without_join_buffer" : 0 .283630005 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .024600489 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .050443503 ,"cost_without_join_buffer" : 0 .308967615 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .050443503 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_outer_2" ,"table" : "t_inner_1" ,"rows_for_plan" : 81 ,"cost_for_plan" : 0 .049238529 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_outer_2,t_inner_1" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 81 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .172815333 ,"cost_without_join_buffer" : 0 .926902845 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .172815333 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_4" ,"plan_details" : {"record_count" : 81 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .067582275 ,"cost_without_join_buffer" : 0 .850890015 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .067582275 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 81 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .172815333 ,"cost_without_join_buffer" : 0 .926902845 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .172815333 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_outer_2,t_inner_1" ,"table" : "t_inner_2" ,"rows_for_plan" : 729 ,"cost_for_plan" : 0 .222053862 ,"semijoin_strategy_choice" : ["strategy" : "FirstMatch" ,"rows" : 27 ,"cost" : 1 .23517089 "strategy" : "SJ-Materialization" ,"rows" : 27 ,"cost" : 0 .083958496 "strategy" : "DuplicateWeedout" ,"prefix_row_count" : 27 ,"tmp_table_rows" : 1 ,"sj_inner_fanout" : 27 ,"rows" : 27 ,"dups_cost" : 0 .222053862 ,"write_cost" : 0 .02934619 ,"full_lookup_cost" : 0 .11734713 ,"total_cost" : 0 .368747182 "chosen_strategy" : "SJ-Materialization" "sj_rows_out" : 0 .333333333 ,"sj_rows_for_plan" : 27 ,"sj_filtered" : 3 .703703704 ,"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_outer_2,t_inner_1,t_inner_2" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_4" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .034460781 ,"cost_without_join_buffer" : 0 .283630005 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .034460781 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .080024379 ,"cost_without_join_buffer" : 0 .308967615 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .080024379 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_outer_2,t_inner_1,t_inner_2" ,"table" : "t_inner_4" ,"rows_for_plan" : 81 ,"cost_for_plan" : 0 .118419277 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_outer_2,t_inner_1,t_inner_2,t_inner_4" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 81 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .261557961 ,"cost_without_join_buffer" : 0 .926902845 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .261557961 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_outer_2,t_inner_1,t_inner_2,t_inner_4" ,"table" : "t_inner_3" ,"rows_for_plan" : 729 ,"cost_for_plan" : 0 .379977238 ,"semijoin_strategy_choice" : ["strategy" : "FirstMatch" ,"rows" : 27 ,"cost" : 1 .294491346 "strategy" : "SJ-Materialization" ,"rows" : 27 ,"cost" : 0 .143278952 "strategy" : "DuplicateWeedout" ,"prefix_row_count" : 27 ,"tmp_table_rows" : 1 ,"sj_inner_fanout" : 27 ,"rows" : 27 ,"dups_cost" : 0 .379977238 ,"write_cost" : 0 .02934619 ,"full_lookup_cost" : 0 .11734713 ,"total_cost" : 0 .526670558 "chosen_strategy" : "SJ-Materialization" "sj_rows_out" : 0 .333333333 ,"sj_rows_for_plan" : 27 ,"sj_filtered" : 3 .703703704 "plan_prefix" : "t_outer_1,t_outer_2,t_inner_1,t_inner_2" ,"table" : "t_inner_3" ,"rows_for_plan" : 243 ,"cost_for_plan" : 0 .163982875 ,"semijoin_strategy_choice" : [],"pruned_by_cost" : true,"current_cost" : 0 .163982875 ,"best_cost" : 0 .143278952 "plan_prefix" : "t_outer_1,t_outer_2,t_inner_1" ,"table" : "t_inner_4" ,"rows_for_plan" : 243 ,"cost_for_plan" : 0 .116820804 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1,t_outer_2,t_inner_1" ,"table" : "t_inner_3" ,"rows_for_plan" : 729 ,"cost_for_plan" : 0 .222053862 ,"semijoin_strategy_choice" : [],"pruned_by_cost" : true,"current_cost" : 0 .222053862 ,"best_cost" : 0 .143278952 "plan_prefix" : "t_outer_1,t_outer_2" ,"table" : "t_inner_2" ,"rows_for_plan" : 243 ,"cost_for_plan" : 0 .075081543 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1,t_outer_2" ,"table" : "t_inner_4" ,"rows_for_plan" : 81 ,"cost_for_plan" : 0 .049238529 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1,t_outer_2" ,"table" : "t_inner_3" ,"rows_for_plan" : 243 ,"cost_for_plan" : 0 .075081543 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1" ,"table" : "t_inner_1" ,"rows_for_plan" : 9 ,"cost_for_plan" : 0 .022028022 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 9 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .024443331 ,"cost_without_join_buffer" : 0 .102989205 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .024443331 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_outer_2" ,"plan_details" : {"record_count" : 9 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .024443331 ,"cost_without_join_buffer" : 0 .102989205 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .024443331 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_4" ,"plan_details" : {"record_count" : 9 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .015203373 ,"cost_without_join_buffer" : 0 .094543335 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .015203373 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 9 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .024443331 ,"cost_without_join_buffer" : 0 .102989205 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .024443331 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1" ,"table" : "t_outer_2" ,"rows_for_plan" : 81 ,"cost_for_plan" : 0 .046471353 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_outer_2" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 81 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .172815333 ,"cost_without_join_buffer" : 0 .926902845 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .172815333 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_4" ,"plan_details" : {"record_count" : 81 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .067582275 ,"cost_without_join_buffer" : 0 .850890015 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .067582275 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 81 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .172815333 ,"cost_without_join_buffer" : 0 .926902845 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .172815333 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_outer_2" ,"table" : "t_inner_2" ,"rows_for_plan" : 729 ,"cost_for_plan" : 0 .219286686 ,"semijoin_strategy_choice" : ["strategy" : "DuplicateWeedout" ,"prefix_row_count" : 3 ,"tmp_table_rows" : 9 ,"sj_inner_fanout" : 27 ,"rows" : 27 ,"dups_cost" : 0 .219286686 ,"write_cost" : 0 .02934619 ,"full_lookup_cost" : 0 .11734713 ,"total_cost" : 0 .365980006 "chosen_strategy" : "DuplicateWeedout" "sj_rows_out" : 0 .333333333 ,"sj_rows_for_plan" : 27 ,"sj_filtered" : 3 .703703704 ,"pruned_by_cost" : true,"current_cost" : 0 .365980006 ,"best_cost" : 0 .143278952 "plan_prefix" : "t_outer_1,t_inner_1,t_outer_2" ,"table" : "t_inner_4" ,"rows_for_plan" : 243 ,"cost_for_plan" : 0 .114053628 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_outer_2,t_inner_4" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 243 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .628673451 ,"cost_without_join_buffer" : 2 .780708535 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .628673451 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 243 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .628673451 ,"cost_without_join_buffer" : 2 .780708535 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .628673451 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_outer_2,t_inner_4" ,"table" : "t_inner_2" ,"rows_for_plan" : 2187 ,"cost_for_plan" : 0 .742727079 ,"semijoin_strategy_choice" : [],"pruned_by_cost" : true,"current_cost" : 0 .742727079 ,"best_cost" : 0 .143278952 "plan_prefix" : "t_outer_1,t_inner_1,t_outer_2,t_inner_4" ,"table" : "t_inner_3" ,"rows_for_plan" : 2187 ,"cost_for_plan" : 0 .742727079 ,"semijoin_strategy_choice" : ["strategy" : "SJ-Materialization" ,"rows" : 81 ,"cost" : 0 .116338225 "chosen_strategy" : "SJ-Materialization" "sj_rows_out" : 0 .333333333 ,"sj_rows_for_plan" : 81 ,"sj_filtered" : 3 .703703704 ,"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_outer_2,t_inner_4,t_inner_3" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 81 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .261557961 ,"cost_without_join_buffer" : 0 .926902845 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .261557961 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_outer_2,t_inner_4,t_inner_3" ,"table" : "t_inner_2" ,"rows_for_plan" : 729 ,"cost_for_plan" : 0 .377896186 ,"semijoin_strategy_choice" : ["strategy" : "DuplicateWeedout" ,"prefix_row_count" : 3 ,"tmp_table_rows" : 9 ,"sj_inner_fanout" : 27 ,"rows" : 27 ,"dups_cost" : 1 .00428504 ,"write_cost" : 0 .02934619 ,"full_lookup_cost" : 0 .11734713 ,"total_cost" : 1 .15097836 "chosen_strategy" : "DuplicateWeedout" "sj_rows_out" : 0 .333333333 ,"sj_rows_for_plan" : 27 ,"sj_filtered" : 3 .703703704 ,"pruned_by_cost" : true,"current_cost" : 1 .15097836 ,"best_cost" : 0 .143278952 "plan_prefix" : "t_outer_1,t_inner_1,t_outer_2" ,"table" : "t_inner_3" ,"rows_for_plan" : 729 ,"cost_for_plan" : 0 .219286686 ,"semijoin_strategy_choice" : [],"pruned_by_cost" : true,"current_cost" : 0 .219286686 ,"best_cost" : 0 .143278952 "plan_prefix" : "t_outer_1,t_inner_1" ,"table" : "t_inner_2" ,"rows_for_plan" : 81 ,"cost_for_plan" : 0 .046471353 ,"semijoin_strategy_choice" : ["strategy" : "FirstMatch" ,"rows" : 3 ,"cost" : 0 .145008465 "strategy" : "SJ-Materialization" ,"rows" : 3 ,"cost" : 0 .065137975 "strategy" : "DuplicateWeedout" ,"prefix_row_count" : 3 ,"tmp_table_rows" : 1 ,"sj_inner_fanout" : 27 ,"rows" : 3 ,"dups_cost" : 0 .046471353 ,"write_cost" : 0 .02548291 ,"full_lookup_cost" : 0 .01303857 ,"total_cost" : 0 .084992833 "chosen_strategy" : "SJ-Materialization" "sj_rows_out" : 0 .333333333 ,"sj_rows_for_plan" : 3 ,"sj_filtered" : 3 .703703704 ,"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_inner_2" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_outer_2" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .017419989 ,"cost_without_join_buffer" : 0 .034329735 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .017419989 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_4" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .012618795 ,"cost_without_join_buffer" : 0 .031514445 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .012618795 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .017419989 ,"cost_without_join_buffer" : 0 .034329735 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .017419989 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_inner_2" ,"table" : "t_outer_2" ,"rows_for_plan" : 27 ,"cost_for_plan" : 0 .082557964 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_inner_2,t_outer_2" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_4" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .034460781 ,"cost_without_join_buffer" : 0 .283630005 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .034460781 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .080024379 ,"cost_without_join_buffer" : 0 .308967615 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .080024379 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_inner_2,t_outer_2" ,"table" : "t_inner_4" ,"rows_for_plan" : 81 ,"cost_for_plan" : 0 .117018745 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_inner_2,t_outer_2,t_inner_4" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 81 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .261557961 ,"cost_without_join_buffer" : 0 .926902845 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .261557961 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_inner_2,t_outer_2,t_inner_4" ,"table" : "t_inner_3" ,"rows_for_plan" : 729 ,"cost_for_plan" : 0 .378576706 ,"semijoin_strategy_choice" : ["strategy" : "FirstMatch" ,"rows" : 27 ,"cost" : 1 .293090814 "strategy" : "SJ-Materialization" ,"rows" : 27 ,"cost" : 0 .14187842 "strategy" : "DuplicateWeedout" ,"prefix_row_count" : 27 ,"tmp_table_rows" : 1 ,"sj_inner_fanout" : 27 ,"rows" : 27 ,"dups_cost" : 0 .378576706 ,"write_cost" : 0 .02934619 ,"full_lookup_cost" : 0 .11734713 ,"total_cost" : 0 .525270026 "chosen_strategy" : "SJ-Materialization" "sj_rows_out" : 0 .333333333 ,"sj_rows_for_plan" : 27 ,"sj_filtered" : 3 .703703704 "plan_prefix" : "t_outer_1,t_inner_1,t_inner_2,t_outer_2" ,"table" : "t_inner_3" ,"rows_for_plan" : 243 ,"cost_for_plan" : 0 .162582343 ,"semijoin_strategy_choice" : [],"pruned_by_cost" : true,"current_cost" : 0 .162582343 ,"best_cost" : 0 .14187842 "plan_prefix" : "t_outer_1,t_inner_1,t_inner_2" ,"table" : "t_inner_4" ,"rows_for_plan" : 9 ,"cost_for_plan" : 0 .07775677 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_inner_2,t_inner_4" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_outer_2" ,"plan_details" : {"record_count" : 9 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .034303623 ,"cost_without_join_buffer" : 0 .102989205 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .034303623 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 9 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .034303623 ,"cost_without_join_buffer" : 0 .102989205 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .034303623 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_inner_2,t_inner_4" ,"table" : "t_outer_2" ,"rows_for_plan" : 81 ,"cost_for_plan" : 0 .112060393 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_inner_2,t_inner_4,t_outer_2" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 81 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .261557961 ,"cost_without_join_buffer" : 0 .926902845 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .261557961 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_inner_2,t_inner_4,t_outer_2" ,"table" : "t_inner_3" ,"rows_for_plan" : 729 ,"cost_for_plan" : 0 .373618354 ,"semijoin_strategy_choice" : ["strategy" : "DuplicateWeedout" ,"prefix_row_count" : 3 ,"tmp_table_rows" : 9 ,"sj_inner_fanout" : 27 ,"rows" : 27 ,"dups_cost" : 0 .373618354 ,"write_cost" : 0 .02934619 ,"full_lookup_cost" : 0 .11734713 ,"total_cost" : 0 .520311674 "chosen_strategy" : "DuplicateWeedout" "sj_rows_out" : 0 .333333333 ,"sj_rows_for_plan" : 27 ,"sj_filtered" : 3 .703703704 ,"pruned_by_cost" : true,"current_cost" : 0 .520311674 ,"best_cost" : 0 .14187842 "plan_prefix" : "t_outer_1,t_inner_1,t_inner_2,t_inner_4" ,"table" : "t_inner_3" ,"rows_for_plan" : 81 ,"cost_for_plan" : 0 .112060393 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1,t_inner_1,t_inner_2" ,"table" : "t_inner_3" ,"rows_for_plan" : 27 ,"cost_for_plan" : 0 .082557964 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1,t_inner_1" ,"table" : "t_inner_4" ,"rows_for_plan" : 27 ,"cost_for_plan" : 0 .037231395 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_inner_4" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_outer_2" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .065233941 ,"cost_without_join_buffer" : 0 .308967615 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .065233941 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .065233941 ,"cost_without_join_buffer" : 0 .308967615 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .065233941 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 27 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .065233941 ,"cost_without_join_buffer" : 0 .308967615 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .065233941 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_inner_4" ,"table" : "t_outer_2" ,"rows_for_plan" : 243 ,"cost_for_plan" : 0 .102465336 ,"semijoin_strategy_choice" : [],"rest_of_plan" : ["plan_prefix" : "t_outer_1,t_inner_1,t_inner_4,t_outer_2" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t_inner_2" ,"plan_details" : {"record_count" : 243 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .628673451 ,"cost_without_join_buffer" : 2 .780708535 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .628673451 ,"uses_join_buffering" : true"best_access_path" : {"table" : "t_inner_3" ,"plan_details" : {"record_count" : 243 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 9 ,"rows_after_filter" : 9 ,"rows_out" : 9 ,"cost" : 0 .628673451 ,"cost_without_join_buffer" : 2 .780708535 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 9 ,"rows_out" : 9 ,"cost" : 0 .628673451 ,"uses_join_buffering" : true"plan_prefix" : "t_outer_1,t_inner_1,t_inner_4,t_outer_2" ,"table" : "t_inner_2" ,"rows_for_plan" : 2187 ,"cost_for_plan" : 0 .731138787 ,"semijoin_strategy_choice" : [],"pruned_by_cost" : true,"current_cost" : 0 .731138787 ,"best_cost" : 0 .14187842 "plan_prefix" : "t_outer_1,t_inner_1,t_inner_4,t_outer_2" ,"table" : "t_inner_3" ,"rows_for_plan" : 2187 ,"cost_for_plan" : 0 .731138787 ,"semijoin_strategy_choice" : [],"pruned_by_cost" : true,"current_cost" : 0 .731138787 ,"best_cost" : 0 .14187842 "plan_prefix" : "t_outer_1,t_inner_1,t_inner_4" ,"table" : "t_inner_2" ,"rows_for_plan" : 243 ,"cost_for_plan" : 0 .102465336 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1,t_inner_1,t_inner_4" ,"table" : "t_inner_3" ,"rows_for_plan" : 243 ,"cost_for_plan" : 0 .102465336 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1,t_inner_1" ,"table" : "t_inner_3" ,"rows_for_plan" : 81 ,"cost_for_plan" : 0 .046471353 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1" ,"table" : "t_inner_2" ,"rows_for_plan" : 27 ,"cost_for_plan" : 0 .02463804 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1" ,"table" : "t_inner_4" ,"rows_for_plan" : 9 ,"cost_for_plan" : 0 .022028022 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "t_outer_1" ,"table" : "t_inner_3" ,"rows_for_plan" : 27 ,"cost_for_plan" : 0 .02463804 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "" ,"table" : "t_outer_2" ,"rows_for_plan" : 9 ,"cost_for_plan" : 0 .011443245 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "" ,"table" : "t_inner_1" ,"rows_for_plan" : 3 ,"cost_for_plan" : 0 .010504815 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "" ,"table" : "t_inner_2" ,"rows_for_plan" : 9 ,"cost_for_plan" : 0 .011443245 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "" ,"table" : "t_inner_4" ,"rows_for_plan" : 3 ,"cost_for_plan" : 0 .010504815 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"plan_prefix" : "" ,"table" : "t_inner_3" ,"rows_for_plan" : 9 ,"cost_for_plan" : 0 .011443245 ,"semijoin_strategy_choice" : [],"pruned_by_heuristic" : true"fix_semijoin_strategies_for_picked_join_order" : ["semi_join_strategy" : "SJ-Materialization" ,"join_order" : ["table" : "t_inner_4" "table" : "t_inner_3" "semi_join_strategy" : "SJ-Materialization" ,"join_order" : ["table" : "t_inner_1" "table" : "t_inner_2" "best_join_order" : ["t_outer_1" ,"<subquery2>" ,"t_outer_2" ,"<subquery3>" "rows" : 27 ,"cost" : 0 .14187842 "substitute_best_equal" : {"condition" : "WHERE" ,"resulting_condition" : "1" "attaching_conditions_to_tables" : {"attached_conditions_computation" : ["condition_on_constant_tables" : "1" ,"computing_condition" : []"attached_conditions_summary" : ["table" : "t_outer_1" ,"attached_condition" : null"table" : "t_inner_1" ,"attached_condition" : null"table" : "t_inner_2" ,"attached_condition" : null"table" : "<subquery2>" ,"attached_condition" : null"table" : "t_outer_2" ,"attached_condition" : null"table" : "t_inner_4" ,"attached_condition" : null"table" : "t_inner_3" ,"attached_condition" : null"table" : "<subquery3>" ,"attached_condition" : null"make_join_readinfo" : []0 0 set @@optimizer_switch= @save_optimizer_switch; ; 18942 : Json_writer::add_bool: Conditional jump or move depends on uninitialised value upon 255 ), FULLTEXT(f)); ; 'fooba' ),('abcde' ),('xyzab' ); SET optimizer_trace = 'enabled=on' ; 'fooba' ); 1 ; ; 18741 : Optimizer trace: multi-part key ranges are printed incorrectly.; 0 ),(1 ),(2 ),(3 ),(4 ),(5 ),(6 ),(7 ),(8 ),(9 ); ; 10 + C.a*100 from t0 A, t0 B, t0 C; ; ; set optimizer_trace='enabled=on' ; 2 and b=4 ; 1 SIMPLE t1 ref a_b a_b 10 const,const 1 Using index'$**.analyzing_range_alternatives' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "range_scan_alternatives" : "index" : "a_b" ,"ranges" : "(2,4) <= (a,b) <= (2,4)" ],"rowid_ordered" : true,"using_mrr" : false,"index_only" : true,"rows" : 1 ,"cost" : 0 .001478954 ,"chosen" : true"analyzing_roworder_intersect" : "cause" : "too few roworder scans" 900 and b between 10 and 20 ; 1 SIMPLE t1 range a_b a_b 10 NULL 107 Using where; Using index '$**.analyzing_range_alternatives' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "range_scan_alternatives" : "index" : "a_b" ,"ranges" : "(900,10) <= (a,b)" ],"rowid_ordered" : false,"using_mrr" : false,"index_only" : true,"rows" : 107 ,"cost" : 0 .016135574 ,"chosen" : true"analyzing_roworder_intersect" : "cause" : "too few roworder scans" ; 100 ), key(start_date, end_date)) ; 7 ) day), 'data' ; '2019-02-10' and end_date <'2019-04-01' ; 1 SIMPLE t1 range start_date start_date 8 NULL 1000 Using index condition'$**.analyzing_range_alternatives' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "range_scan_alternatives" : "index" : "start_date" ,"ranges" : "(2019-02-10,NULL) < (start_date,end_date)" ],"rowid_ordered" : false,"using_mrr" : false,"index_only" : false,"rows" : 1000 ,"cost" : 1 .235690484 ,"chosen" : true"analyzing_roworder_intersect" : "cause" : "too few roworder scans" ; ; 0 ),(1 ),(2 ),(3 ),(4 ),(5 ),(6 ),(7 ),(8 ),(9 ); not null,not null,not null,not null,; ; 1 and 4 and b < 50 ; 1 SIMPLE t1 range a_b_c a_b_c 8 NULL 4 Using index condition'$**.analyzing_range_alternatives' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "range_scan_alternatives" : "index" : "a_b_c" ,"ranges" : "(1) <= (a,b) < (4,50)" ],"rowid_ordered" : false,"using_mrr" : false,"index_only" : false,"rows" : 4 ,"cost" : 0 .00627616 ,"chosen" : true"analyzing_roworder_intersect" : "cause" : "too few roworder scans" ; 16 ), INDEX i_b(b)); 1 , x'D95B94336A9946A39CF5B58CFE772D8C' ); 2 , NULL); 0 xD95B94336A9946A39CF5B58CFE772D8C); 1 SIMPLE t1 ref i_b i_b 17 const 1 Using index condition'$**.analyzing_range_alternatives' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "range_scan_alternatives" : "index" : "i_b" ,"ranges" : "(\\xD9[\\x943j\\x99F\\xA3\\x9C\\xF5\\xB5\\x8C\\xFEw-\\x8C) <= (b) <= (\\xD9[\\x943j\\x99F\\xA3\\x9C\\xF5\\xB5\\x8C\\xFEw-\\x8C)" ],"rowid_ordered" : true,"using_mrr" : false,"index_only" : false,"rows" : 1 ,"cost" : 0 .002574553 ,"chosen" : true"analyzing_roworder_intersect" : "cause" : "too few roworder scans" ; 1 SIMPLE t1 ref i_b i_b 17 const 1 Using index condition'$**.analyzing_range_alternatives' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "range_scan_alternatives" : "index" : "i_b" ,"ranges" : "(NULL) <= (b) <= (NULL)" ],"rowid_ordered" : true,"using_mrr" : false,"index_only" : false,"rows" : 1 ,"cost" : 0 .002574553 ,"chosen" : true"analyzing_roworder_intersect" : "cause" : "too few roworder scans" ; 18880 : Optimizer trace prints date in hexadecimal10 ) CHARSET BINARY , INDEX i_b(b)); 1 , 'ab\n' ); 2 , NULL); set optimizer_trace=1 ; 'ab\n' ; 1 SIMPLE t1 ref i_b i_b 13 const 1 Using index condition'$**.analyzing_range_alternatives' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "range_scan_alternatives" : "index" : "i_b" ,"ranges" : "(ab\\x0A) <= (b) <= (ab\\x0A)" ],"rowid_ordered" : true,"using_mrr" : false,"index_only" : false,"rows" : 1 ,"cost" : 0 .002574553 ,"chosen" : true"analyzing_roworder_intersect" : "cause" : "too few roworder scans" 10 ) AFTER i; 'ab\n' ; 1 SIMPLE t1 ref i_b i_b 11 const 1 Using index condition'$**.analyzing_range_alternatives' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "range_scan_alternatives" : "index" : "i_b" ,"ranges" : "(ab\\x0A\\x00\\x00\\x00\\x00\\x00\\x00\\x00) <= (b) <= (ab\\x0A\\x00\\x00\\x00\\x00\\x00\\x00\\x00)" ],"rowid_ordered" : true,"using_mrr" : false,"index_only" : false,"rows" : 1 ,"cost" : 0 .002574553 ,"chosen" : true"analyzing_roworder_intersect" : "cause" : "too few roworder scans" 10 ) AFTER i; 'ab\n' ; 1 SIMPLE t1 ref i_b i_b 13 const 1 Using index condition'$**.analyzing_range_alternatives' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "range_scan_alternatives" : "index" : "i_b" ,"ranges" : "(ab\\x0A) <= (b) <= (ab\\x0A)" ],"rowid_ordered" : true,"using_mrr" : false,"index_only" : false,"rows" : 1 ,"cost" : 0 .002574553 ,"chosen" : true"analyzing_roworder_intersect" : "cause" : "too few roworder scans" ; 10 ), INDEX i_b(b)) CHARSET=latin1; 1 , 'ab\n' ); 2 , NULL); 'ab\n' ; 1 SIMPLE t1 ref i_b i_b 11 const 1 Using index condition'$**.analyzing_range_alternatives' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "range_scan_alternatives" : "index" : "i_b" ,"ranges" : "(ab\\n) <= (b) <= (ab\\n)" ],"rowid_ordered" : true,"using_mrr" : false,"index_only" : false,"rows" : 1 ,"cost" : 0 .002574553 ,"chosen" : true"analyzing_roworder_intersect" : "cause" : "too few roworder scans" ; ; 1071 Specified key was too long; max key length is 1000 bytes 1 , 'ab\n' ); 2 , NULL); set optimizer_trace=1 ; 'ab\n' ; 1 SIMPLE t1 ref i_b i_b 1003 const 1 Using where'$**.analyzing_range_alternatives' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "range_scan_alternatives" : "index" : "i_b" ,"ranges" : "(ab\\x0A) <= (b) <= (ab\\x0A)" ],"rowid_ordered" : false,"using_mrr" : false,"index_only" : false,"rows" : 1 ,"cost" : 0 .002574553 ,"chosen" : true"analyzing_roworder_intersect" : "cause" : "too few roworder scans" ; 10 ), INDEX i_b(b)) CHARSET=latin1; 1 , 'ab\n' ); 2 , 'ab\n' ); set optimizer_trace=1 ; 'ab\n' ; 1 SIMPLE t1 ref i_b i_b 13 const 2 Using index condition'$**.analyzing_range_alternatives' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "range_scan_alternatives" : "index" : "i_b" ,"ranges" : "(ab\\n) <= (b) <= (ab\\n)" ],"rowid_ordered" : true,"using_mrr" : false,"index_only" : false,"rows" : 2 ,"cost" : 0 .003808422 ,"chosen" : true"analyzing_roworder_intersect" : "cause" : "too few roworder scans" ; ; 0 ),(1 ),(2 ),(3 ),(4 ),(5 ),(6 ),(7 ),(8 ),(9 ); ; 10 + C.a*100 from t0 A, t0 B, t0 C; 100 ), key(start_date, end_date)) ; 7 ) day), 'data' ; '2019-02-10' and end_date <'2019-04-01' ; "query_block" : {"select_id" : 1 ,"cost" : 1 .235690484 ,"nested_loop" : ["table" : {"table_name" : "t1" ,"access_type" : "range" ,"possible_keys" : ["start_date" ],"key" : "start_date" ,"key_length" : "8" ,"used_key_parts" : ["start_date" , "end_date" ],"loops" : 1 ,"rows" : 1000 ,"cost" : 1 .235690484 ,"filtered" : 100 ,"index_condition" : "t1.start_date >= '2019-02-10' and t1.end_date < '2019-04-01'" '$**.analyzing_range_alternatives' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "range_scan_alternatives" : "index" : "start_date" ,"ranges" : "(2019-02-10,NULL) < (start_date,end_date)" ],"rowid_ordered" : false,"using_mrr" : false,"index_only" : false,"rows" : 1000 ,"cost" : 1 .235690484 ,"chosen" : true"analyzing_roworder_intersect" : "cause" : "too few roworder scans" ; 19776 : Assertion `to_len >= 8 ' failed in convert_to_printable with optimizer trace enabled 16 ) NOT NULL, KEY(f)); 'a' ),('b' ); SET optimizer_trace = 'enabled=on' ; 'x' ; ; and rows_for_plan for join prefix; 0 ),(1 ),(2 ),(3 ),(4 ),(5 ),(6 ),(7 ),(8 ),(9 ); ; 10 + C.a*100 , A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C; ; set @tmp_jcl=@@join_cache_level; set join_cache_level=0 ; set optimizer_trace=1 ; not as trivial. So,5 and B.a<800 ; 1 SIMPLE A ALL NULL NULL NULL NULL 10 Using where1 SIMPLE B ALL NULL NULL NULL NULL 1000 Using where'$**.considered_execution_plans' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "plan_prefix" : "" ,"get_costs_for_tables" : "best_access_path" : "table" : "A" ,"plan_details" : "record_count" : 1 "considered_access_paths" : "access_type" : "scan" ,"rows" : 10 ,"rows_after_filter" : 5 ,"rows_out" : 5 ,"cost" : 0 .01159965 ,"index_only" : false,"chosen" : true"chosen_access_method" : "type" : "scan" ,"rows_read" : 5 ,"rows_out" : 5 ,"cost" : 0 .01159965 ,"uses_join_buffering" : false"best_access_path" : "table" : "B" ,"plan_details" : "record_count" : 1 "considered_access_paths" : "access_type" : "scan" ,"rows" : 1000 ,"rows_after_filter" : 800 ,"rows_out" : 800 ,"cost" : 0 .1669214 ,"index_only" : false,"chosen" : true"chosen_access_method" : "type" : "scan" ,"rows_read" : 800 ,"rows_out" : 800 ,"cost" : 0 .1669214 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "A" ,"rows_for_plan" : 5 ,"cost_for_plan" : 0 .01159965 ,"rest_of_plan" : "plan_prefix" : "A" ,"get_costs_for_tables" : "best_access_path" : "table" : "B" ,"plan_details" : "record_count" : 5 "considered_access_paths" : "access_type" : "scan" ,"rows" : 1000 ,"rows_after_filter" : 800 ,"rows_out" : 800 ,"cost" : 0 .8329686 ,"index_only" : false,"chosen" : true"chosen_access_method" : "type" : "scan" ,"rows_read" : 800 ,"rows_out" : 800 ,"cost" : 0 .8329686 ,"uses_join_buffering" : false"plan_prefix" : "A" ,"table" : "B" ,"rows_for_plan" : 4000 ,"cost_for_plan" : 0 .84456825 "plan_prefix" : "" ,"table" : "B" ,"rows_for_plan" : 800 ,"cost_for_plan" : 0 .1669214 ,"pruned_by_heuristic" : trueset join_cache_level=@tmp_jcl; and B.a<800 ; 1 SIMPLE A ALL NULL NULL NULL NULL 10 Using where1 SIMPLE B ref b b 5 test.A.a 1 Using where'$**.considered_execution_plans' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "plan_prefix" : "" ,"get_costs_for_tables" : "best_access_path" : "table" : "A" ,"plan_details" : "record_count" : 1 "considered_access_paths" : "access_type" : "scan" ,"rows" : 10 ,"rows_after_filter" : 10 ,"rows_out" : 10 ,"cost" : 0 .01159965 ,"index_only" : false,"chosen" : true"chosen_access_method" : "type" : "scan" ,"rows_read" : 10 ,"rows_out" : 10 ,"cost" : 0 .01159965 ,"uses_join_buffering" : false"best_access_path" : "table" : "B" ,"plan_details" : "record_count" : 1 "considered_access_paths" : "access_type" : "scan" ,"rows" : 1000 ,"rows_after_filter" : 800 ,"rows_out" : 800 ,"cost" : 0 .1669214 ,"index_only" : false,"chosen" : true"chosen_access_method" : "type" : "scan" ,"rows_read" : 800 ,"rows_out" : 800 ,"cost" : 0 .1669214 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "A" ,"rows_for_plan" : 10 ,"cost_for_plan" : 0 .01159965 ,"rest_of_plan" : "plan_prefix" : "A" ,"get_costs_for_tables" : "best_access_path" : "table" : "B" ,"plan_details" : "record_count" : 10 "considered_access_paths" : "access_type" : "ref" ,"index" : "b" ,"used_range_estimates" : false,"reason" : "not available" ,"rows" : 1 ,"cost" : 0 .01901531 ,"chosen" : true"type" : "scan" ,"chosen" : false,"cause" : "cost" "chosen_access_method" : "type" : "ref" ,"index" : "b" ,"rows_read" : 1 ,"rows_out" : 1 ,"cost" : 0 .01901531 ,"uses_join_buffering" : false"plan_prefix" : "A" ,"table" : "B" ,"rows_for_plan" : 10 ,"cost_for_plan" : 0 .03061496 ,"pushdown_cond_selectivity" : 0 .8 ,"filtered" : 80 ,"rows_out" : 0 .8 ,"selectivity" : 0 .8 ,"estimated_join_cardinality" : 8 "plan_prefix" : "" ,"table" : "B" ,"rows_for_plan" : 800 ,"cost_for_plan" : 0 .1669214 ,"pruned_by_cost" : true,"current_cost" : 0 .1669214 ,"best_cost" : 0 .03061496 ; 8 ' failed in convert_to_printable 255 ))); 'foo' ), ('bar' ); 'a' , 0 ); 1 SIMPLE t1 ref a a 258 const 1 Using where'a' , 0 ); '$**.analyzing_range_alternatives' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "range_scan_alternatives" : "index" : "a" ,"ranges" : "() <= (a) <= ()" ],"rowid_ordered" : false,"using_mrr" : false,"index_only" : false,"rows" : 1 ,"cost" : 0 .002574553 ,"chosen" : true"analyzing_roworder_intersect" : "cause" : "too few roworder scans" ; ; 1 ),(2 ),(3 ),(4 ),(5 ); ; 1 ),(2 ),(3 ),(4 ),(5 ); ; 1 ),(2 ),(3 ),(4 ),(5 ),(6 ),(7 ),(8 ),(9 ),(10 ); ; 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 5 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) '$**.semijoin_table_pullout' )) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; '$**.semijoin_table_pullout' ))"pulled_out_tables" : "t2" ,"t1" ; 22401 : Optimizer trace: multi-component range is not printed correctly; 1 ,1 ),(1 ,5 ),(5 ,1 ),(5 ,5 ); set optimizer_trace=1 ; 2 and kp2 >=4 ); '$**.range_scan_alternatives' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "index" : "kp1" ,"ranges" : "(2,4) <= (kp1,kp2) <= (2)" ],"rowid_ordered" : false,"using_mrr" : false,"index_only" : true,"rows" : 1 ,"cost" : 0 .001478954 ,"chosen" : true; 21626 : Optimizer misses the details about the picked join order; ; ; ; SET OPTIMIZER_TRACE=1 ; ; 1 SIMPLE t1 ALL a NULL NULL NULL 10 Using where; Using temporary; Using filesort 1 SIMPLE t2 ref a a 5 test.t1.a 1 '$**.considered_execution_plans' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "plan_prefix" : "" ,"get_costs_for_tables" : "best_access_path" : "table" : "t1" ,"plan_details" : "record_count" : 1 "considered_access_paths" : "access_type" : "scan" ,"rows" : 10 ,"rows_after_filter" : 10 ,"rows_out" : 10 ,"cost" : 0 .01159965 ,"index_only" : false,"chosen" : true"chosen_access_method" : "type" : "scan" ,"rows_read" : 10 ,"rows_out" : 10 ,"cost" : 0 .01159965 ,"uses_join_buffering" : false"best_access_path" : "table" : "t2" ,"plan_details" : "record_count" : 1 "considered_access_paths" : "access_type" : "scan" ,"rows" : 100 ,"rows_after_filter" : 100 ,"rows_out" : 100 ,"cost" : 0 .0256761 ,"index_only" : false,"chosen" : true,"use_tmp_table" : true"chosen_access_method" : "type" : "scan" ,"rows_read" : 100 ,"rows_out" : 100 ,"cost" : 0 .0256761 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 10 ,"cost_for_plan" : 0 .01159965 ,"rest_of_plan" : "plan_prefix" : "t1" ,"get_costs_for_tables" : "best_access_path" : "table" : "t2" ,"plan_details" : "record_count" : 10 "considered_access_paths" : "access_type" : "ref" ,"index" : "a" ,"used_range_estimates" : false,"reason" : "not available" ,"rows" : 1 ,"cost" : 0 .01840091 ,"chosen" : true"type" : "scan" ,"chosen" : false,"cause" : "cost" "chosen_access_method" : "type" : "ref" ,"index" : "a" ,"rows_read" : 1 ,"rows_out" : 1 ,"cost" : 0 .01840091 ,"uses_join_buffering" : false"plan_prefix" : "t1" ,"table" : "t2" ,"rows_for_plan" : 10 ,"cost_for_plan" : 0 .03000056 ,"cost_for_sorting" : 0 .006368384 "plan_prefix" : "" ,"table" : "t2" ,"rows_for_plan" : 100 ,"cost_for_plan" : 0 .0256761 ,"rest_of_plan" : "plan_prefix" : "t2" ,"get_costs_for_tables" : "best_access_path" : "table" : "t1" ,"plan_details" : "record_count" : 100 "considered_access_paths" : "access_type" : "ref" ,"index" : "a" ,"used_range_estimates" : false,"reason" : "not available" ,"rows" : 1 ,"cost" : 0 .1821659 ,"chosen" : true"access_type" : "scan_with_join_cache" ,"rows" : 10 ,"rows_after_filter" : 10 ,"rows_out" : 1 ,"cost" : 0 .11055225 ,"cost_without_join_buffer" : 1 .159965 ,"index_only" : false,"chosen" : true"chosen_access_method" : "type" : "scan" ,"rows_read" : 10 ,"rows_out" : 1 ,"cost" : 0 .11055225 ,"uses_join_buffering" : true"plan_prefix" : "t2" ,"table" : "t1" ,"rows_for_plan" : 100 ,"cost_for_plan" : 0 .13622835 ,"pruned_by_cost" : true,"current_cost" : 0 .13622835 ,"best_cost" : 0 .036368944 ; 22665 : Print ranges in the optimizer trace created for non-indexed columns when2 ; ; SET optimizer_trace=1 ; ; 1 and 5 and b <= 5 ; 1 SIMPLE t1 ALL NULL NULL NULL NULL 100 0 .25 Using where1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` between 1 and 5 and `test`.`t1`.`b` <= 5 '$**.selectivity_for_columns' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "column_name" : "a" ,"ranges" : "1 <= a <= 5" ],"selectivity_from_histogram" : 0 .05 "column_name" : "b" ,"ranges" : "NULL < b <= 5" ],"selectivity_from_histogram" : 0 .05 5 ; 1 SIMPLE t1 ALL NULL NULL NULL NULL 100 99 .00 Using where1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` <> 5 '$**.selectivity_for_columns' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "column_name" : "a" ,"ranges" : "NULL < a < 5" ,"5 < a" "selectivity_from_histogram" : 0 .99 10 and b < 25 ; 1 SIMPLE t1 ALL NULL NULL NULL NULL 100 15 .00 Using where1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` >= 10 and `test`.`t1`.`b` < 25 '$**.selectivity_for_columns' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "column_name" : "b" ,"ranges" : "10 <= b < 25" ],"selectivity_from_histogram" : 0 .15 ; 22910 :SIGSEGV in Opt_trace_context::is_started & SIGSEGV in Json_writer::add_table_name; 2 ,3 ); 101 ; 1 SET b=10 WHERE a=1 ; '$**.range_scan_alternatives' )) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE; "index" : "PRIMARY" ,"ranges" : "(1) <= (a) <= (1)" ],"rowid_ordered" : true,"using_mrr" : false,"index_only" : false,"rows" : 1 ,"cost" : 0 .002483968 ,"chosen" : true; set optimizer_trace='enabled=off' ; 24975 Server consumes extra 4 G memory upon querying INFORMATION_SCHEMA.OPTIIMIZER_TRACEset max_session_mem_used=1024 *1024 *1024 ; ; ; set max_session_mem_used=default; 22380 Assertion `name.length == strlen(name.str)' failed in Item::print_item_w_name on SELECT w/ optimizer_trace enabled SET optimizer_trace="enabled=on" ; 'a\0' LIMIT 0 ; ; 'a\0' LIMIT 0 {"steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["expanded_query" : "select 'a\\0' AS `a\\x00` limit 0" "join_optimization" : {"select_id" : 1 ,"steps" : []SET optimizer_trace=DEFAULT; 27238 : Assertion `got_name == named_item_expected()' failed in Json_writer::on_start_object ; SET optimizer_trace=1 ; 0 ,0 ); ; ; 30964 : MAX_SEL_ARG memory exhaustion is not visible in the optimizer trace; ; 1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ,12 ,13 ,14 ,15 ,16 ) and c2=1 ) and 1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ) and 1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ) and 1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ) and 1 ,2 ,3 ,4 ); 1 SIMPLE t1 index c1 c1 40 NULL 3 Using where; Using index '$**.setup_range_conditions' )); '$**.setup_range_conditions' ))"enforce_sel_arg_weight_limit" : "index" : "c1" ,"old_weight" : 74806 ,"new_weight" : 1776 "sel_arg_alloc_limit_hit" : "alloced_sel_args" : 16001 ; 31085 : multi-update using view with optimizer trace enabledSET SESSION optimizer_trace = 'enabled=on' ; ; 1 AS c UNION SELECT 2 AS c; 0 ,4 ),(5 ,6 ); SET t.b = t.a, t.a = v.c WHERE v.c < t.a; ; SET t.b = t.a, t.a = v.c WHERE v.c < t.a {"steps" : ["view" : {"table" : "v" ,"select_id" : 2 ,"algorithm" : "materialized" "join_preparation" : {"select_id" : 2 ,"steps" : ["expanded_query" : "/* select#2 */ select 1 AS c" "join_preparation" : {"select_id" : 3 ,"steps" : ["expanded_query" : "/* select#3 */ select 2 AS c" "join_preparation" : {"select_id" : "fake" ,"steps" : ["expanded_query" : "select c AS c from dual" "join_preparation" : {"select_id" : 1 ,"steps" : ["expanded_query" : "/* select#1 */ update t join v set t.b = t.a,t.a = v.c where v.c < t.a" "join_optimization" : {"select_id" : 1 ,"steps" : ["condition_processing" : {"condition" : "WHERE" ,"original_condition" : "v.c < t.a" ,"steps" : ["transformation" : "equality_propagation" ,"resulting_condition" : "v.c < t.a" "transformation" : "constant_propagation" ,"resulting_condition" : "v.c < t.a" "transformation" : "trivial_condition_removal" ,"resulting_condition" : "v.c < t.a" "join_optimization" : {"select_id" : 2 ,"steps" : []"join_optimization" : {"select_id" : 3 ,"steps" : []"ref_optimizer_key_uses" : []"rows_estimation" : ["table" : "t" ,"table_scan" : {"rows" : 2 ,"read_cost" : 0 .01028441 ,"read_and_compare_cost" : 0 .01034841 "table" : "<derived2>" ,"table_scan" : {"rows" : 2 ,"read_cost" : 0 .010020701 ,"read_and_compare_cost" : 0 .010089369 "table_dependencies" : ["table" : "t" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"table" : "<derived2>" ,"row_may_be_null" : false,"map_bit" : 1 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 2 ,"rows_after_filter" : 2 ,"rows_out" : 2 ,"cost" : 0 .01034841 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 2 ,"rows_out" : 2 ,"cost" : 0 .01034841 ,"uses_join_buffering" : false"best_access_path" : {"table" : "<derived2>" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 2 ,"rows_after_filter" : 2 ,"rows_out" : 2 ,"cost" : 0 .010089369 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 2 ,"rows_out" : 2 ,"cost" : 0 .010089369 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t" ,"rows_for_plan" : 2 ,"cost_for_plan" : 0 .01034841 ,"rest_of_plan" : ["plan_prefix" : "t" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "<derived2>" ,"plan_details" : {"record_count" : 2 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 2 ,"rows_after_filter" : 2 ,"rows_out" : 2 ,"cost" : 0 .010582565 ,"cost_without_join_buffer" : 0 .020178738 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 2 ,"rows_out" : 2 ,"cost" : 0 .010582565 ,"uses_join_buffering" : true"plan_prefix" : "t" ,"table" : "<derived2>" ,"rows_for_plan" : 4 ,"cost_for_plan" : 0 .020930975 "plan_prefix" : "" ,"table" : "<derived2>" ,"rows_for_plan" : 2 ,"cost_for_plan" : 0 .010089369 ,"rest_of_plan" : ["plan_prefix" : "<derived2>" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t" ,"plan_details" : {"record_count" : 2 "considered_access_paths" : ["access_type" : "scan_with_join_cache" ,"rows" : 2 ,"rows_after_filter" : 2 ,"rows_out" : 2 ,"cost" : 0 .010841606 ,"cost_without_join_buffer" : 0 .02069682 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 2 ,"rows_out" : 2 ,"cost" : 0 .010841606 ,"uses_join_buffering" : true"plan_prefix" : "<derived2>" ,"table" : "t" ,"rows_for_plan" : 4 ,"cost_for_plan" : 0 .020930975 ,"pruned_by_cost" : true,"current_cost" : 0 .020930975 ,"best_cost" : 0 .020930975 "best_join_order" : ["t" , "<derived2>" ],"rows" : 4 ,"cost" : 0 .020930975 "substitute_best_equal" : {"condition" : "WHERE" ,"resulting_condition" : "v.c < t.a" "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "t" ,"attached_condition" : null"table" : "<derived2>" ,"attached_condition" : "v.c < t.a" "make_join_readinfo" : []"join_optimization" : {"select_id" : "fake" ,"steps" : ["rows_estimation" : ["table" : "union" ,"table_scan" : {"rows" : 2 ,"read_cost" : 0 .010020701 ,"read_and_compare_cost" : 0 .010084701 "table_dependencies" : ["table" : "union" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "union" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 2 ,"rows_after_filter" : 2 ,"rows_out" : 2 ,"cost" : 0 .010084701 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 2 ,"rows_out" : 2 ,"cost" : 0 .010084701 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "union" ,"rows_for_plan" : 2 ,"cost_for_plan" : 0 .010084701 "best_join_order" : ["union" ],"rows" : 2 ,"cost" : 0 .010084701 "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "union" ,"attached_condition" : null"make_join_readinfo" : []0 0 ; 0 4 1 5 SET optimizer_trace=DEFAULT; ; ; 26301 : Split optimization improvements: Optimizer Trace coverage; ; ; ; ; ; ; ; ; ; ; ; set optimizer_trace=1 ; ; 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 1 PRIMARY t2 ref a a 5 test.t1.b 2 Using where1 PRIMARY t3 ref a a 5 test.t1.b 3 Using where1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 1 Using where2 LATERAL DERIVED t10 ref grp_id grp_id 5 test.t1.b 100 2 LATERAL DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) '$**.check_split_materialized' )) as JS; "split_candidates" : "t10.grp_id" ]'$**.choose_best_splitting' )'$[0].split_plan_search[0]' ; "considered_keys" : "table_name" : "t10" ,"index" : "grp_id" ,"rec_per_key" : 100 ,"param_tables" : 1 "refills" : 5 ,"spl_pd_boundary" : 2 ,"split_plan_search" : "split_materialized" : "table" : "t10" ,"key" : "grp_id" ,"org_cost" : 1 .159965 ,"postjoin_cost" : 4 .020888502 ,"one_splitting_cost" : 5 .180853502 ,"unsplit_postjoin_cost" : 159 .1588125 ,"unsplit_cost" : 275 .1553125 ,"rows" : 100 ,"refills" : 5 ,"total_splitting_cost" : 25 .90426751 ,"chosen" : true; set optimizer_trace=DEFAULT; 29179 Condition pushdown from HAVING into WHERE is not shown in optimizer trace1 ), KEY (a), KEY(b,a)) ENGINE=MEMORY; 4 ,'n' ),(1 ,'h' ),(NULL,'w' ); SET optimizer_trace= 'enabled=on' ; 'p' OR a = 4 GROUP BY b, a HAVING a <= 7 ; 1 4 '$**.steps[*].join_optimization.steps[*].condition_pushdown_from_having' ) ) exp1, JSON_VALID(trace) exp2 FROM information_schema.optimizer_trace; "conds" : "(t1.b <> 'p' or multiple equal(4, t1.a)) and t1.a <= 7" ,"having" : null1 ; 30334 Optimizer trace produces invalid JSON with WHERE subquerySET optimizer_trace= 'enabled=on' ; ; 1 ),(2 ); ; 3 ),(4 ); ; 1 2 ; 1 ; 10 .4 testsset optimizer_trace='enabled=on' ; ; 10000000 ; "steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["expanded_query" : "select count(0) AS `count(*)` from seq_1_to_10000000" "join_optimization" : {"select_id" : 1 ,"steps" : ["rows_estimation" : ["table" : "seq_1_to_10000000" ,"table_scan" : {"rows" : 10000000 ,"read_cost" : 124 .7880673 ,"read_and_compare_cost" : 444 .7880673 "table_dependencies" : ["table" : "seq_1_to_10000000" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "seq_1_to_10000000" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 10000000 ,"rows_after_filter" : 10000000 ,"rows_out" : 10000000 ,"cost" : 444 .7880673 ,"index_only" : true,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 10000000 ,"rows_out" : 10000000 ,"cost" : 444 .7880673 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "seq_1_to_10000000" ,"rows_for_plan" : 10000000 ,"cost_for_plan" : 444 .7880673 "best_join_order" : ["seq_1_to_10000000" ],"rows" : 10000000 ,"cost" : 444 .7880673 "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "seq_1_to_10000000" ,"attached_condition" : null"make_join_readinfo" : []0 0 22891 : Optimizer trace: const tables are not clearly visible; 0 ),(1 ),(2 ),(3 ),(4 ),(5 ),(6 ),(7 ),(8 ),(9 ); ; ; ; ; ; ; 1000 ) on t2.a=t1.a and t2.pk is null; 1 SIMPLE t3 const PRIMARY NULL NULL NULL 1 Impossible ON condition1 SIMPLE t2 const PRIMARY NULL NULL NULL 0 Impossible ON condition1 SIMPLE t1 ALL NULL NULL NULL NULL 10 '$**.mark_join_nest_as_const' )) as jd; "members" : "t3" ,"t2" ; 23767 : IN-to-subquery conversion is not visible in optimizer trace; 0 ),(1 ),(2 ),(3 ),(4 ),(5 ),(6 ),(7 ),(8 ),(9 ); set @tmp=@@in_predicate_conversion_threshold; set in_predicate_conversion_threshold=3 ; 1 ,2 ,3 ,4 ,5 ,6 ); 1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where1 PRIMARY <derived3> eq_ref distinct_key distinct_key 4 test.t0.a 1 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used'$**.in_to_subquery_conversion' )) as jd; "item" : "t0.a in (1,2,3,4,5,6)" ,"conversion" : "join_preparation" : "select_id" : 2 ,"steps" : "derived" : "table" : "tvc_0" ,"select_id" : 3 ,"algorithm" : "materialized" "transformation" : "select_id" : 2 ,"from" : "IN (SELECT)" ,"to" : "semijoin" ,"chosen" : true"expanded_query" : "/* select#2 */ select tvc_0._col_1 from (values (1),(2),(3),(4),(5),(6)) tvc_0" 1 ,2 ,3 ,4 ,5 ,a+1 ); 1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where'$**.in_to_subquery_conversion' )) as jd; "item" : "t0.a in (1,2,3,4,5,t0.a + 1)" ,"done" : false,"reason" : "non-constant element in the IN-list" '1' ,'2' ,'3' ,'4' ,'5' ,'6' ); 1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where'$**.in_to_subquery_conversion' )) as jd; "item" : "t0.a in ('1','2','3','4','5','6')" ,"done" : false,"reason" : "type mismatch" set in_predicate_conversion_threshold=@tmp; ; 29298 : INSERT ... SELECT Does not produce an optimizer trace; ; 1 ,1 ), (2 ,2 ), (3 ,3 ), (4 ,4 ), (5 ,5 ); set optimizer_trace=1 ; and a>4 ; 1 from information_schema.optimizer_trace; 1 and a>4 1 ; 34305 Redundant truncation errors/warnings with optimizer_trace enabledSET @@optimizer_trace='enabled=on' ; 2 ) NOT NULL PRIMARY KEY,20 ) NOT NULL,; 2 ) NOT NULL PRIMARY KEY,20 ) NOT NULL,; 'AB' ,'MySQLAB' ),'JA' ,'Sun Microsystems' ),'MS' ,'Microsoft' ),'IB' ,'IBM- Inc.' ),'GO' ,'Google Inc.' ); 'AB' ,'Sweden' ),'JA' ,'USA' ),'MS' ,'United States' ),'IB' ,'North America' ),'GO' ,'South America' ); SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'Unknown%' ; ; 10 .5 tests23677 : Optimizer trace ... (test coverage); 0 ,0 ,0 ),(1 ,1 ,1 ),(2 ,2 ,2 ),(3 ,3 ,3 ),(4 ,4 ,4 ); ; 1 ,1 ),(2 ,2 ); ; 0 ,0 ,0 ),(1 ,1 ,1 ),(2 ,2 ,2 ),(3 ,3 ,3 ),(4 ,4 ,4 ); and t1.a=t3.a; 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where1 SIMPLE t3 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t1 ref PRIMARY PRIMARY 8 test.t3.a,test.t2.b 1 Using indexset @trace=(select trace from information_schema.optimizer_trace); set @path= (select json_search(@trace, 'one' , 'no predicate for first keypart' )); set @sub_path= substr(@path, 2 , locate('.best_access_path' , @path)-2 ); ; 1 ].join_optimization.steps[4 ].considered_execution_plans[1 ].rest_of_plan[0 ].get_costs_for_tables[0 ]'.best_access_path.considered_access_paths[0]' ); "access_type" : "ref" ,"index" : "PRIMARY" ,"chosen" : false,"cause" : "no predicate for first keypart" ; 23645 : Optimizer trace: print conditions after substitute_for_best_equal_field; 1 ,1 ,1 ),(2 ,2 ,2 ); ; ; ; ; 3 10 ; 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort '$**.substitute_best_equal' )); '$**.substitute_best_equal' ))"condition" : "WHERE" ,"resulting_condition" : "t1.a = 3" "condition" : "HAVING" ,"resulting_condition" : "t1.a + t1.b < 10" and t2.a<3 5555 ; 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) '$**.substitute_best_equal' )); '$**.substitute_best_equal' ))"condition" : "WHERE" ,"resulting_condition" : "t1.b > 5555" "condition" : "ON expr" ,"attached_to" : "t2" ,"resulting_condition" : "t2.a = t1.a and t1.a < 3" and t3.a=t2.a and t3.a + t2.a <1000 5555 ; 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (incremental, BNL join) '$**.substitute_best_equal' )); '$**.substitute_best_equal' ))"condition" : "WHERE" ,"resulting_condition" : "t1.b > 5555" "condition" : "ON expr" ,"attached_to" : "t2" ,"resulting_condition" : "t2.a = t1.a and t3.a = t1.a and t1.a + t1.a < 1000" 23646 : Optimizer trace: optimize_cond() should show ON expression processing'$**.condition_processing' )); '$**.condition_processing' ))"condition" : "WHERE" ,"original_condition" : "t1.b > 5555" ,"steps" : "build_equal_items" : "condition" : "ON expr" ,"attached_to" : "t3" ,"resulting_condition" : "t3.a + t2.a < 1000 and multiple equal(t2.a, t1.a, t3.a)" "transformation" : "equality_propagation" ,"resulting_condition" : "t1.b > 5555" "transformation" : "constant_propagation" ,"resulting_condition" : "t1.b > 5555" "transformation" : "trivial_condition_removal" ,"resulting_condition" : "t1.b > 5555" ; 24325 : Optimizer trace doesn't cover LATERAL DERIVED ; 8 ,3 ), (5 ,7 ), (1 ,2 ), (2 ,1 ), (9 ,7 ), (7 ,5 ), (2 ,2 ), (7 ,3 ),9 ,3 ), (8 ,1 ), (4 ,5 ), (2 ,3 ); 127 ), index idx_a(a)) engine=myisam charset=latin1; 7 ,10 ,'x' ), (1 ,20 ,'a' ), (2 ,23 ,'b' ), (7 ,18 ,'z' ), (1 ,30 ,'c' ),4 ,71 ,'d' ), (3 ,15 ,'x' ), (7 ,82 ,'y' ), (8 ,12 ,'t' ), (4 ,15 ,'b' ),11 ,33 ,'a' ), (10 ,42 ,'u' ), (4 ,53 ,'p' ), (10 ,17 ,'r' ), (2 ,90 ,'x' ),17 ,10 ,'s' ), (11 ,20 ,'v' ), (12 ,23 ,'y' ), (17 ,18 ,'a' ), (11 ,30 ,'d' ),24 ,71 ,'h' ), (23 ,15 ,'i' ), (27 ,82 ,'k' ), (28 ,12 ,'p' ), (24 ,15 ,'q' ),31 ,33 ,'f' ), (30 ,42 ,'h' ), (40 ,53 ,'m' ), (30 ,17 ,'o' ), (21 ,90 ,'b' ),37 ,10 ,'e' ), (31 ,20 ,'g' ), (32 ,23 ,'f' ), (37 ,18 ,'n' ), (41 ,30 ,'l' ),54 ,71 ,'j' ), (53 ,15 ,'w' ), (57 ,82 ,'z' ), (58 ,12 ,'k' ), (54 ,15 ,'p' ),61 ,33 ,'c' ), (60 ,42 ,'a' ), (62 ,53 ,'x' ), (67 ,17 ,'g' ), (64 ,90 ,'v' ); 10 , b+10 , concat(c,'f' ) from t2; ; min (t2.c) as m from t2 group by t2.a) t3 ; 1 PRIMARY t1 range idx_b idx_b 5 NULL 4 Using index condition; Using where 1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 2 LATERAL DERIVED t2 ref idx_a idx_a 5 test.t1.a 1 '$**.choose_best_splitting' )) ; '$**.choose_best_splitting' ))"considered_keys" : "considered_keys" : "table_name" : "t2" ,"index" : "idx_a" ,"rec_per_key" : 1 .8367 ,"param_tables" : 1 "refills" : 4 ,"spl_pd_boundary" : 2 ,"split_plan_search" : "considered_execution_plans" : "plan_prefix" : "" ,"get_costs_for_tables" : "best_access_path" : "table" : "t2" ,"plan_details" : "record_count" : 1 "considered_access_paths" : "access_type" : "ref" ,"index" : "idx_a" ,"used_range_estimates" : false,"reason" : "not available" ,"rows" : 1 .8367 ,"cost" : 0 .002051185 ,"chosen" : true"type" : "scan" ,"chosen" : false,"cause" : "cost" "chosen_access_method" : "type" : "ref" ,"index" : "idx_a" ,"rows_read" : 1 .8367 ,"rows_out" : 1 .8367 ,"cost" : 0 .002051185 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "t2" ,"rows_for_plan" : 1 .8367 ,"cost_for_plan" : 0 .002051185 ,"cost_for_sorting" : 0 .001155201 "split_materialized" : "table" : "t2" ,"key" : "idx_a" ,"org_cost" : 0 .002051185 ,"postjoin_cost" : 0 .001135418 ,"one_splitting_cost" : 0 .003186603 ,"unsplit_postjoin_cost" : 0 .036032575 ,"unsplit_cost" : 0 .060625425 ,"rows" : 1 .8367 ,"refills" : 4 ,"total_splitting_cost" : 0 .012746412 ,"chosen" : true; ; 1 , '{\"1\": 1}' ), (2 , '{\"1\": 2}' ), (3 , '{\"1\": 3}' ),4 , '{\"1\": 4}' ), (5 , '{\"1\": 5}' ), (6 , '{\"1\": 6}' ); "$" COLUMNS (jf FOR ORDINALITY)) AS tbl); 1 {"1" : 1 }2 {"1" : 2 }3 {"1" : 3 }4 {"1" : 4 }5 {"1" : 5 }6 {"1" : 6 }'$**.best_join_order' )); '$**.best_join_order' ))"t1" ,"<subquery2>" ; 27306 : SET STATEMENT optimizer_trace=1 Doesn't save the trace set optimizer_trace=0 ; set statement optimizer_trace=1 for select * from seq_1_to_10 where seq<2 ; 1 not be empty:100 ) from information_schema.optimizer_trace; 100 )"steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : [set optimizer_trace='enabled=off' ; 10 .6 tests36461 : Remove join_execution nodes and add range_check_for_each_record when appropriate; 1 , 999 ),(999 , 1 ),(987 ,987 ); ; ; ; ; set optimizer_trace='enabled=on' ; and t2.b<t1.b and t2.b+t1.a>t3.a; a1 PRIMARY t3 ALL NULL NULL NULL NULL 2 2 .00 100 .00 100 .00 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 3 .00 100 .00 100 .00 2 DEPENDENT SUBQUERY t2 ALL a,b NULL NULL NULL 1000 333 .33 100 .00 98 .60 Range checked for each record (index map: 0 x3)6 objects with select_id 2 and loop varying from 1 ..2 for each of the 3 ranges from t1:set @trace=(select trace from information_schema.optimizer_trace); set @trace=(select json_extract(@trace, '$**.range-checked-for-each-record' )); set @trace=json_replace(@trace, '$[0].rows_estimation[0].range_analysis' ,'REPLACED' ); set @trace=json_replace(@trace, '$[1].rows_estimation[0].range_analysis' ,'REPLACED' ); set @trace=json_replace(@trace, '$[2].rows_estimation[0].range_analysis' ,'REPLACED' ); set @trace=json_replace(@trace, '$[3].rows_estimation[0].range_analysis' ,'REPLACED' ); set @trace=json_replace(@trace, '$[4].rows_estimation[0].range_analysis' ,'REPLACED' ); set @trace=json_replace(@trace, '$[5].rows_estimation[0].range_analysis' ,'REPLACED' ); ; "select_id" : 2 ,"loop" : 1 ,"rows_estimation" : "table" : "t2" ,"range_analysis" : "REPLACED" "select_id" : 2 ,"loop" : 1 ,"rows_estimation" : "table" : "t2" ,"range_analysis" : "REPLACED" "select_id" : 2 ,"loop" : 1 ,"rows_estimation" : "table" : "t2" ,"range_analysis" : "REPLACED" "select_id" : 2 ,"loop" : 2 ,"rows_estimation" : "table" : "t2" ,"range_analysis" : "REPLACED" "select_id" : 2 ,"loop" : 2 ,"rows_estimation" : "table" : "t2" ,"range_analysis" : "REPLACED" "select_id" : 2 ,"loop" : 2 ,"rows_estimation" : "table" : "t2" ,"range_analysis" : "REPLACED" '$**.join_execution' )); '$**.join_execution' ))1 <t1.a and t2.b+1 <t1.b and t2.b+t1.a>t3.a; 985 1 985 2 '$**.range-checked-for-each-record' )) as TRACE; '$**.join_execution' )); '$**.join_execution' ))set optimizer_trace='enabled=off' ; ; 30354 : Fix JSON escaping in optimizer trace outputset @saved_optimizer_trace=@@optimizer_trace; set optimizer_trace='enabled=on' ; 1 : Simple double quotes"a" ); "a" )1 '$**.expanded_query' )) AS expanded_query FROM information_schema.optimizer_trace; 1 ["select octet_length('a') AS `LENGTH(\" a\")`" ]2 : Nested quotes"a\" b"); "a\" b") 3 '$**.expanded_query' )) FROM information_schema.optimizer_trace; '$**.expanded_query' ))1 ["select octet_length('a\" b') AS `LENGTH(\"a\\\"b\")`"] 3 : Special characters with quotes"a\n\" b\t\"c" ); "a\n\" b\t\"c" )7 '$**.expanded_query' )) FROM information_schema.optimizer_trace; '$**.expanded_query' ))1 ["select octet_length('a\\n\" b\\t\"c') AS `LENGTH(\" a\\n\\\"b\\t\\\" c\")`" ]4 : New line"foo" , "New " AS CHAR)); "foo" , "New " AS CHAR)) "foo" :"New\u000Aline" }'$**.expanded_query' )) FROM information_schema.optimizer_trace; '$**.expanded_query' ))1 ["select column_json(column_create('foo','New\\nline' AS char charset latin1 collate latin1_swedish_ci )) AS `COLUMN_JSON(COLUMN_CREATE(\" foo\", \" New\nline\" AS CHAR))`" ]5 : Backslashes"a\\b" ); "a\\b" )3 '$**.expanded_query' )) FROM information_schema.optimizer_trace; '$**.expanded_query' ))1 ["select octet_length('a\\\\b') AS `LENGTH(\" a\\\\b\")`" ]6 : JSON functions'{"key":"value"}' , "$.key" ); '{"key":"value"}' , "$.key" )"value" '$**.expanded_query' )) FROM information_schema.optimizer_trace; '$**.expanded_query' ))1 ["select json_extract('{\" key\":\" value\"}','$.key') AS `JSON_EXTRACT('{\" key\":\" value\"}', \" $.key\")`" ]7 : Failed escape due to illegal symbolSET NAMES utf8mb4; "��" AS x; '$**.expanded_query' )) FROM information_schema.optimizer_trace; '$**.expanded_query' ))1 ["select '��' AS x" ]SET NAMES DEFAULT; set optimizer_trace=@saved_optimizer_trace; 10 .11 testsset @save_optimizer_switch= @@optimizer_switch; set @save_use_stat_tables= @@use_stat_tables; set @save_histogram_size= @@histogram_size; set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; set optimizer_switch='rowid_filter=on' ; set use_stat_tables='preferably' ; set histogram_size=127 ; ; 2 , seq/10 from seq_1_to_1000; ; 10 and b between 10 and 50 and c < 10 ; 1 SIMPLE t1 range|filter a,b a|b 5 |5 NULL 9 (2 %) Using index condition; Using where; Using rowid filter 10 and b between 10 and 50 and c < 10 {"steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["expanded_query" : "select t1.a AS a,t1.b AS b,t1.c AS c from t1 where t1.a < 10 and t1.b between 10 and 50 and t1.c < 10" "join_optimization" : {"select_id" : 1 ,"steps" : ["condition_processing" : {"condition" : "WHERE" ,"original_condition" : "t1.a < 10 and t1.b between 10 and 50 and t1.c < 10" ,"steps" : ["transformation" : "equality_propagation" ,"resulting_condition" : "t1.a < 10 and t1.b between 10 and 50 and t1.c < 10" "transformation" : "constant_propagation" ,"resulting_condition" : "t1.a < 10 and t1.b between 10 and 50 and t1.c < 10" "transformation" : "trivial_condition_removal" ,"resulting_condition" : "t1.a < 10 and t1.b between 10 and 50 and t1.c < 10" "ref_optimizer_key_uses" : []"rows_estimation" : ["table" : "t1" ,"range_analysis" : {"table_scan" : {"rows" : 1000 ,"cost" : 0 .1671618 "potential_range_indexes" : ["index" : "a" ,"usable" : true,"key_parts" : ["a" ]"index" : "b" ,"usable" : true,"key_parts" : ["b" ]"setup_range_conditions" : [],"analyzing_range_alternatives" : {"range_scan_alternatives" : ["index" : "a" ,"ranges" : ["(NULL) < (a) < (10)" ],"rowid_ordered" : false,"using_mrr" : false,"index_only" : false,"rows" : 9 ,"cost" : 0 .012445505 ,"chosen" : true"index" : "b" ,"ranges" : ["(10) <= (b) <= (50)" ],"rowid_ordered" : false,"using_mrr" : false,"index_only" : false,"rows" : 21 ,"cost" : 0 .027251933 ,"chosen" : false,"cause" : "cost" "analyzing_roworder_intersect" : {"cause" : "too few roworder scans" "group_index_range" : {"chosen" : false,"cause" : "no group by or distinct" "chosen_range_access_summary" : {"range_access_plan" : {"type" : "range_scan" ,"index" : "a" ,"rows" : 9 ,"ranges" : ["(NULL) < (a) < (10)" ]"rows_for_plan" : 9 ,"cost_for_plan" : 0 .012445505 ,"chosen" : true"table" : "t1" ,"rowid_filters" : ["key" : "a" ,"build_cost" : 0 .001846537 ,"rows" : 9 "key" : "b" ,"build_cost" : 0 .003322634 ,"rows" : 21 "selectivity_for_indexes" : ["index_name" : "a" ,"selectivity_from_index" : 0 .009 "index_name" : "b" ,"selectivity_from_index" : 0 .021 "selectivity_for_columns" : ["column_name" : "c" ,"ranges" : ["NULL < c < 10" ],"selectivity_from_histogram" : 0 .094 "cond_selectivity" : 0 .000017766 "table_dependencies" : ["table" : "t1" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["filter" : {"rowid_filter_index" : "b" ,"index_only_cost" : 0 .001605807 ,"filter_startup_cost" : 0 .003322634 ,"find_key_and_filter_lookup_cost" : 6 .695354 e-4 ,"filter_selectivity" : 0 .021 ,"original_rows" : 9 ,"new_rows" : 0 .189 ,"original_access_cost" : 0 .011607363 ,"with_filter_access_cost" : 0 .002485375 ,"original_found_rows_cost" : 0 .010001556 ,"with_filter_found_rows_cost" : 2 .100327 e-4 ,"org_cost" : 0 .011895363 ,"filter_cost" : 0 .005814057 ,"filter_used" : true"access_type" : "range" ,"range_index" : "a" ,"rows" : 9 ,"rows_after_filter" : 0 .189 ,"rows_out" : 0 .017766 ,"cost" : 0 .006364199 ,"chosen" : true"chosen_access_method" : {"type" : "range" ,"index" : "a" ,"rows_read" : 0 .189 ,"rows_out" : 0 .017766 ,"cost" : 0 .006364199 ,"uses_join_buffering" : false,"rowid_filter_index" : "b" "plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 0 .017766 ,"cost_for_plan" : 0 .006364199 ,"pushdown_cond_selectivity" : 0 .094 ,"filtered" : 0 .1974 ,"rows_out" : 0 .017766 "best_join_order" : ["t1" ],"rows" : 0 .017766 ,"cost" : 0 .006364199 "table" : "t1" ,"range_analysis" : {"potential_range_indexes" : ["index" : "a" ,"usable" : false,"cause" : "not applicable" "index" : "b" ,"usable" : true,"key_parts" : ["b" ]"setup_range_conditions" : [],"analyzing_range_alternatives" : {"range_scan_alternatives" : ["index" : "b" ,"ranges" : ["(10) <= (b) <= (50)" ],"rowid_ordered" : false,"using_mrr" : false,"index_only" : true,"rows" : 21 ,"cost" : 0 .004244354 ,"chosen" : true"chosen_range_access_summary" : {"range_access_plan" : {"type" : "range_scan" ,"index" : "b" ,"rows" : 21 ,"ranges" : ["(10) <= (b) <= (50)" ]"rows_for_plan" : 21 ,"cost_for_plan" : 0 .004244354 ,"chosen" : true"substitute_best_equal" : {"condition" : "WHERE" ,"resulting_condition" : "t1.a < 10 and t1.b between 10 and 50 and t1.c < 10" "attaching_conditions_to_tables" : {"attached_conditions_computation" : [],"attached_conditions_summary" : ["table" : "t1" ,"attached_condition" : "t1.a < 10 and t1.b between 10 and 50 and t1.c < 10" "make_join_readinfo" : ["table" : "t1" ,"index_condition" : "t1.a < 10" ,"row_condition" : "t1.b between 10 and 50 and t1.c < 10" 0 0 ; ; 1 ),(2 ),(3 ); ; 10 ), seq, seq from seq_1_to_10000; ; set optimizer_use_condition_selectivity=2 ; and t1.b<5000 and t1.c<1000 ; "query_block" : {"select_id" : 1 ,"cost" : 2 .661782024 ,"nested_loop" : ["table" : {"table_name" : "three" ,"access_type" : "ALL" ,"loops" : 1 ,"rows" : 3 ,"cost" : 0 .010504815 ,"filtered" : 100 ,"attached_condition" : "three.a is not null" "table" : {"table_name" : "t1" ,"access_type" : "ref" ,"possible_keys" : ["a" , "b" ],"key" : "a" ,"key_length" : "5" ,"used_key_parts" : ["a" ],"ref" : ["test.three.a" ],"rowid_filter" : {"range" : {"key" : "b" ,"used_key_parts" : ["b" ]"rows" : 4312 ,"selectivity_pct" : 43 .12 "loops" : 3 ,"rows" : 1000 ,"cost" : 2 .651277209 ,"filtered" : 43 .11999893 ,"attached_condition" : "t1.b < 5000 and t1.c < 1000" set optimizer_use_condition_selectivity=4 ; and t1.b<5000 and t1.c<1000 ; "query_block" : {"select_id" : 1 ,"cost" : 1 .712236739 ,"nested_loop" : ["table" : {"table_name" : "three" ,"access_type" : "ALL" ,"loops" : 1 ,"rows" : 3 ,"cost" : 0 .010504815 ,"filtered" : 100 "block-nl-join" : {"table" : {"table_name" : "t1" ,"access_type" : "ALL" ,"possible_keys" : ["a" , "b" ],"loops" : 3 ,"rows" : 10000 ,"cost" : 1 .701731924 ,"filtered" : 3 .230766058 ,"attached_condition" : "t1.b < 5000 and t1.c < 1000" "buffer_type" : "flat" ,"buffer_size" : "65" ,"join_type" : "BNL" ,"attached_condition" : "t1.a = three.a" and t1.b<5000 and t1.c<1000 {"steps" : ["join_preparation" : {"select_id" : 1 ,"steps" : ["expanded_query" : "select three.a AS a,t1.a AS a,t1.b AS b,t1.c AS c from three join t1 where t1.a = three.a and t1.b < 5000 and t1.c < 1000" "join_optimization" : {"select_id" : 1 ,"steps" : ["condition_processing" : {"condition" : "WHERE" ,"original_condition" : "t1.a = three.a and t1.b < 5000 and t1.c < 1000" ,"steps" : ["transformation" : "equality_propagation" ,"resulting_condition" : "t1.b < 5000 and t1.c < 1000 and multiple equal(t1.a, three.a)" "transformation" : "constant_propagation" ,"resulting_condition" : "t1.b < 5000 and t1.c < 1000 and multiple equal(t1.a, three.a)" "transformation" : "trivial_condition_removal" ,"resulting_condition" : "t1.b < 5000 and t1.c < 1000 and multiple equal(t1.a, three.a)" "ref_optimizer_key_uses" : ["table" : "t1" ,"index" : "a" ,"field" : "a" ,"equals" : "three.a" ,"null_rejecting" : true"rows_estimation" : ["table" : "three" ,"table_scan" : {"rows" : 3 ,"read_cost" : 0 .010408815 ,"read_and_compare_cost" : 0 .010504815 "table" : "t1" ,"range_analysis" : {"table_scan" : {"rows" : 10000 ,"cost" : 1 .581538 "potential_range_indexes" : ["index" : "a" ,"usable" : false,"cause" : "not applicable" "index" : "b" ,"usable" : true,"key_parts" : ["b" ]"setup_range_conditions" : [],"analyzing_range_alternatives" : {"range_scan_alternatives" : ["index" : "b" ,"ranges" : ["(NULL) < (b) < (5000)" ],"rowid_ordered" : false,"using_mrr" : false,"index_only" : false,"rows" : 4312 ,"cost" : 5 .324909012 ,"chosen" : false,"cause" : "cost" "analyzing_roworder_intersect" : {"cause" : "too few roworder scans" "group_index_range" : {"chosen" : false,"cause" : "not single_table" "table" : "t1" ,"rowid_filters" : ["key" : "b" ,"build_cost" : 0 .611716709 ,"rows" : 4312 "selectivity_for_indexes" : ["index_name" : "b" ,"selectivity_from_index" : 0 .4312 "selectivity_for_columns" : ["column_name" : "c" ,"ranges" : ["NULL < c < 1000" ],"selectivity_from_histogram" : 0 .0999 "cond_selectivity" : 0 .04307688 "table_dependencies" : ["table" : "three" ,"row_may_be_null" : false,"map_bit" : 0 ,"depends_on_map_bits" : []"table" : "t1" ,"row_may_be_null" : false,"map_bit" : 1 ,"depends_on_map_bits" : []"considered_execution_plans" : ["plan_prefix" : "" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "three" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 3 ,"rows_after_filter" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 3 ,"rows_out" : 3 ,"cost" : 0 .010504815 ,"uses_join_buffering" : false"best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 1 "considered_access_paths" : ["access_type" : "scan" ,"rows" : 10000 ,"rows_after_filter" : 430 .7688 ,"rows_out" : 430 .7688 ,"cost" : 1 .581538 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 430 .7688 ,"rows_out" : 430 .7688 ,"cost" : 1 .581538 ,"uses_join_buffering" : false"plan_prefix" : "" ,"table" : "three" ,"rows_for_plan" : 3 ,"cost_for_plan" : 0 .010504815 ,"rest_of_plan" : ["plan_prefix" : "three" ,"get_costs_for_tables" : ["best_access_path" : {"table" : "t1" ,"plan_details" : {"record_count" : 3 "considered_access_paths" : ["access_type" : "ref" ,"index" : "a" ,"used_range_estimates" : false,"reason" : "not available" ,"filter" : {"rowid_filter_index" : "b" ,"index_only_cost" : 0 .092096742 ,"filter_startup_cost" : 0 .611716709 ,"find_key_and_filter_lookup_cost" : 0 .094772697 ,"filter_selectivity" : 0 .4312 ,"original_rows" : 1000 ,"new_rows" : 431 .2 ,"original_access_cost" : 1 .203380742 ,"with_filter_access_cost" : 0 .6660551 ,"original_found_rows_cost" : 1 .111284 ,"with_filter_found_rows_cost" : 0 .479185661 ,"org_cost" : 3 .706142226 ,"filter_cost" : 2 .651277209 ,"filter_used" : true"rows" : 431 .2 ,"cost" : 2 .651277209 ,"chosen" : true"access_type" : "scan_with_join_cache" ,"rows" : 10000 ,"rows_after_filter" : 430 .7688 ,"rows_out" : 323 .0766 ,"cost" : 1 .701731924 ,"cost_without_join_buffer" : 4 .7319164 ,"index_only" : false,"chosen" : true"chosen_access_method" : {"type" : "scan" ,"rows_read" : 430 .7688 ,"rows_out" : 323 .0766 ,"cost" : 1 .701731924 ,"uses_join_buffering" : true"plan_prefix" : "three" ,"table" : "t1" ,"rows_for_plan" : 969 .2298 ,"cost_for_plan" : 1 .712236739 ,"pushdown_cond_selectivity" : 0 .75 ,"filtered" : 3 .230766 ,"rows_out" : 323 .0766 "plan_prefix" : "" ,"table" : "t1" ,"rows_for_plan" : 430 .7688 ,"cost_for_plan" : 1 .581538 ,"pruned_by_heuristic" : true"best_join_order" : ["three" , "t1" ],"rows" : 969 .2298 ,"cost" : 1 .712236739 "substitute_best_equal" : {"condition" : "WHERE" ,"resulting_condition" : "t1.a = three.a and t1.b < 5000 and t1.c < 1000" "attaching_conditions_to_tables" : {"attached_conditions_computation" : ["table" : "t1" ,"range_analysis" : {"table_scan" : {"rows" : 10000 ,"cost" : 1 .581538 "potential_range_indexes" : ["index" : "a" ,"usable" : true,"key_parts" : ["a" ]"index" : "b" ,"usable" : true,"key_parts" : ["b" ]"setup_range_conditions" : [],"analyzing_range_alternatives" : {"range_scan_alternatives" : ["index" : "a" "index" : "b" ,"ranges" : ["(NULL) < (b) < (5000)" ],"rowid_ordered" : false,"using_mrr" : false,"index_only" : false,"rows" : 4312 ,"cost" : 5 .324909012 ,"chosen" : false,"cause" : "cost" "analyzing_roworder_intersect" : {"cause" : "too few roworder scans" "group_index_range" : {"chosen" : false,"cause" : "not single_table" "attached_conditions_summary" : ["table" : "three" ,"attached_condition" : null"table" : "t1" ,"attached_condition" : "t1.a = three.a and t1.b < 5000 and t1.c < 1000" "make_join_readinfo" : []0 0 ; 21095 : Index condition push down is not reflected in optimizer trace; ; 3 and b!=5 and c<10 ; "query_block" : {"select_id" : 1 ,"cost" : 0 .003808422 ,"nested_loop" : ["table" : {"table_name" : "t10" ,"access_type" : "range" ,"possible_keys" : ["a" ],"key" : "a" ,"key_length" : "5" ,"used_key_parts" : ["a" ],"loops" : 1 ,"rows" : 2 ,"cost" : 0 .003808422 ,"filtered" : 100 ,"index_condition" : "t10.a < 3 and t10.b <> 5" ,"attached_condition" : "t10.c < 10" set optimizer_trace='enabled=on' ; 3 and b!=5 and c<10 ; 1 1 1 2 2 2 '$**.attaching_conditions_to_tables' )) as out1 ; "attached_conditions_computation" : "attached_conditions_summary" : "table" : "t10" ,"attached_condition" : "t10.a < 3 and t10.b <> 5 and t10.c < 10" ; 21092 : EXISTS to IN is not reflected in the optimizer traceset optimizer_trace='enabled=on' ; 10 ), cn_a int ); ; ; 1 from t2 where ci_p > 100000 and cn_c = ci_c)OR (cn_n LIKE 'L%' ) )AND cn_a > 1000000 ; '$.steps[*].join_optimization[0].steps[0].transformation' ); "select_id" : 2 ,"from" : "EXISTS (SELECT)" ,"to" : "IN (SELECT)" ,"upper_not" : false; 29997 Partition Pruning not included in optimizer tracing10 ; 10 ; ; ; set optimizer_trace='enabled=on' ; 2 ,3 ,4 ) and t3.a in (4 ,5 ); 1 SIMPLE t3 p4,p5 ALL NULL NULL NULL NULL 2 Using where1 SIMPLE t2 p2,p3,p4 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) '$**.prune_partitions' )) as out1; "table" : "t2" ,"used_partitions" : "p2,p3,p4" "table" : "t3" ,"used_partitions" : "p4,p5" ; 10 ),20 ),25 ); 5 ),(15 ),(22 ); 28 ; 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables'$**.prune_partitions' )) as out1 ; "table" : "t1" ,"used_partitions" : "" ; 39768 : I_S.OPTIMIZER_TRACE.TRACE should use utf8mb4; NOT NULL,SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,20 ) NOT NULL,1 ) NOT NULL0 39720 : Optimizer Trace may have invalid JSON due to quoting.100 ); set optimizer_trace=1 ; '"' ; 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tablesset @trace=(select trace from information_schema.optimizer_trace); ; 1 ; set @@optimizer_switch= @save_optimizer_switch; set @@use_stat_tables= @save_use_stat_tables; set @@histogram_size= @save_histogram_size; set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
Messung V0.5 in Prozent C=96 H=100 G=97
¤ Diese beiden folgenden Angebotsgruppen bietet das Unternehmen2.676Angebot
(Wie Sie bei der Firma Beratungs- und Dienstleistungen beauftragen können 2026-10-08)
¤ *Eine klare Vorstellung vom Zielzustand
