diff options
Diffstat (limited to 'mysql-test/main/opt_trace.result')
| -rw-r--r-- | mysql-test/main/opt_trace.result | 232 |
1 files changed, 175 insertions, 57 deletions
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 7b71152d246..1c138e81b02 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -457,6 +457,11 @@ select * from v2 { ] }, { + "check_split_materialized": { + "not_applicable": "no candidate field can be accessed through ref" + } + }, + { "best_join_order": ["t1"] }, { @@ -780,6 +785,11 @@ explain select * from v1 { ] }, { + "check_split_materialized": { + "not_applicable": "group list has no candidates" + } + }, + { "best_join_order": ["t1"] }, { @@ -8929,6 +8939,110 @@ SET optimizer_trace=DEFAULT; DROP VIEW v; DROP TABLE t; # +# MDEV-26301: Split optimization improvements: Optimizer Trace coverage +# +create table t1(a int, b int); +insert into t1 select seq,seq from seq_1_to_5; +create table t2(a int, b int, key(a)); +insert into t2 +select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B; +create table t3(a int, b int, key(a)); +insert into t3 +select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B; +analyze table t1,t2,t3 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status Table is already up to date +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status Table is already up to date +create table t10 ( +grp_id int, +col1 int, +key(grp_id) +); +insert into t10 +select +A.seq, +B.seq +from +seq_1_to_100 A, +seq_1_to_100 B; +create table t11 ( +col1 int, +col2 int +); +insert into t11 +select A.seq, A.seq from seq_1_to_10 A; +analyze table t10,t11 persistent for all; +Table Op Msg_type Msg_text +test.t10 analyze status Engine-independent statistics collected +test.t10 analyze status Table is already up to date +test.t11 analyze status Engine-independent statistics collected +test.t11 analyze status OK +set optimizer_trace=1; +explain +select * from +( +(t1 left join t2 on t2.a=t1.b) +left join t3 on t3.a=t1.b +) left join (select grp_id, count(*) +from t10 left join t11 on t11.col1=t10.col1 +group by grp_id) T on T.grp_id=t1.b; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY t2 ref a a 5 test.t1.b 2 Using where +1 PRIMARY t3 ref a a 5 test.t1.b 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 10 Using where +2 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) +select json_detailed(json_extract(trace, '$**.check_split_materialized')) as JS +from information_schema.optimizer_trace; +JS +[ + { + "split_candidates": + ["t10.grp_id"] + } +] +select +json_detailed( +json_remove( +json_extract(trace, '$**.choose_best_splitting') +, '$[0].split_plan_search[0]' + ) +) as JS +from information_schema.optimizer_trace; +JS +[ + { + "considered_keys": + [ + { + "table_name": "t10", + "index": "grp_id", + "rec_per_key": 100, + "param_tables": 1 + } + ], + "refills": 5, + "spl_pd_boundary": 2, + "split_plan_search": + [], + "lead_table": "t10", + "index": "grp_id", + "parts": 1, + "split_sel": 0.001, + "cost": 2535.968504, + "unsplit_cost": 253440.0075, + "records": 100, + "chosen": true + } +] +drop table t1,t2,t3,t10,t11; +set optimizer_trace=DEFAULT; +# # End of 10.4 tests # set optimizer_trace='enabled=on'; @@ -9341,69 +9455,73 @@ from information_schema.optimizer_trace; json_detailed(json_extract(trace, '$**.choose_best_splitting')) [ - [ - { - "considered_execution_plans": - [ - { - "plan_prefix": - [], - "table": "t2", - "best_access_path": + { + "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": + [ { - "considered_access_paths": - [ + "plan_prefix": + [], + "table": "t2", + "best_access_path": + { + "considered_access_paths": + [ + { + "access_type": "ref", + "index": "idx_a", + "used_range_estimates": false, + "reason": "not available", + "rows": 1.8367, + "cost": 2.000585794, + "chosen": true + }, + { + "type": "scan", + "chosen": false, + "cause": "cost" + } + ], + "chosen_access_method": { - "access_type": "ref", - "index": "idx_a", - "used_range_estimates": false, - "reason": "not available", - "rows": 1.8367, + "type": "ref", + "records": 1.8367, "cost": 2.000585794, - "chosen": true - }, - { - "type": "scan", - "chosen": false, - "cause": "cost" + "uses_join_buffering": false } - ], - "chosen_access_method": - { - "type": "ref", - "records": 1.8367, - "cost": 2.000585794, - "uses_join_buffering": false - } - }, - "rows_for_plan": 1.8367, - "cost_for_plan": 2.367925794, - "cost_for_sorting": 1.8367 - } - ] - }, - { - "best_splitting": - { - "table": "t2", - "key": "idx_a", - "record_count": 4, - "cost": 2.488945919, - "unsplit_cost": 25.72361682 + }, + "rows_for_plan": 1.8367, + "cost_for_plan": 2.367925794, + "cost_for_sorting": 1.8367 + } + ] } - } - ] -] -select -json_detailed(json_extract(trace, '$**.lateral_derived')) -from -information_schema.optimizer_trace; -json_detailed(json_extract(trace, '$**.lateral_derived')) -[ + ], + "lead_table": "t2", + "index": "idx_a", + "parts": 1, + "split_sel": 0.020407778, + "cost": 2.488945919, + "unsplit_cost": 25.72361682, + "records": 1, + "chosen": true + }, { - "startup_cost": 9.955783677, - "splitting_cost": 2.488945919, - "records": 1 + "considered_keys": + [] } ] drop table t1,t2; |