diff options
Diffstat (limited to 'mysql-test/main/analyze_format_json.result')
| -rw-r--r-- | mysql-test/main/analyze_format_json.result | 177 |
1 files changed, 172 insertions, 5 deletions
diff --git a/mysql-test/main/analyze_format_json.result b/mysql-test/main/analyze_format_json.result index 02635a8f3dd..0e1ea88404b 100644 --- a/mysql-test/main/analyze_format_json.result +++ b/mysql-test/main/analyze_format_json.result @@ -201,8 +201,11 @@ ANALYZE "buffer_type": "flat", "buffer_size": "1Kb", "join_type": "BNL", + "r_loops": 20, "r_filtered": 100, - "r_unpack_time_ms": "REPLACED" + "r_unpack_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "r_effective_rows": 60 } } ] @@ -257,8 +260,11 @@ ANALYZE "buffer_size": "1Kb", "join_type": "BNL", "attached_condition": "tbl1.c > tbl2.c", + "r_loops": 20, "r_filtered": 15.83333333, - "r_unpack_time_ms": "REPLACED" + "r_unpack_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "r_effective_rows": 60 } } ] @@ -836,8 +842,11 @@ ANALYZE "buffer_size": "65", "join_type": "BNL", "attached_condition": "<in_optimizer>(t2.b,t2.b in (subquery#2))", + "r_loops": 2, "r_filtered": null, - "r_unpack_time_ms": "REPLACED" + "r_unpack_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "r_effective_rows": 0 } } ], @@ -950,8 +959,11 @@ ANALYZE "buffer_type": "flat", "buffer_size": "1", "join_type": "BNL", + "r_loops": 2, "r_filtered": null, - "r_unpack_time_ms": "REPLACED" + "r_unpack_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "r_effective_rows": 0 } } ], @@ -994,8 +1006,11 @@ ANALYZE "buffer_size": "65", "join_type": "BNL", "attached_condition": "t2.f2 = t3.f3", + "r_loops": 0, "r_filtered": null, - "r_unpack_time_ms": "REPLACED" + "r_unpack_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "r_effective_rows": null } } ] @@ -1089,3 +1104,155 @@ ANALYZE } } drop table t0,t1,t2; +# +# MDEV-30806: ANALYZE FORMAT=JSON: better support for BNL and BNL-H joins +# +create table t10 ( +a int, +b int +); +insert into t10 select seq, seq/3 from seq_0_to_999; +create table t11 ( +a int, +b int +); +insert into t11 select seq, seq/5 from seq_0_to_999; +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 OK +test.t11 analyze status Engine-independent statistics collected +test.t11 analyze status OK +analyze format=json +select * from t10, t11 +where +t10.a < 700 and +t11.a < 100 +and t10.b=t11.b; +ANALYZE +{ + "query_optimization": { + "r_total_time_ms": "REPLACED" + }, + "query_block": { + "select_id": 1, + "cost": "REPLACED", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "t11", + "access_type": "ALL", + "loops": 1, + "r_loops": 1, + "rows": 1000, + "r_rows": 1000, + "cost": "REPLACED", + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 10, + "r_filtered": 10, + "attached_condition": "t11.a < 100" + } + }, + { + "block-nl-join": { + "table": { + "table_name": "t10", + "access_type": "ALL", + "loops": 100, + "r_loops": 1, + "rows": 1000, + "r_rows": 1000, + "cost": "REPLACED", + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 70, + "r_filtered": 70, + "attached_condition": "t10.a < 700" + }, + "buffer_type": "flat", + "buffer_size": "1Kb", + "join_type": "BNL", + "attached_condition": "t10.b = t11.b", + "r_loops": 100, + "r_filtered": 0.424285714, + "r_unpack_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "r_effective_rows": 700 + } + } + ] + } +} +set @tmp=@@join_cache_level, join_cache_level=6; +analyze format=json +select * from t10, t11 +where +t10.a < 700 and +t11.a < 100 +and t10.b=t11.b; +ANALYZE +{ + "query_optimization": { + "r_total_time_ms": "REPLACED" + }, + "query_block": { + "select_id": 1, + "cost": "REPLACED", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "t11", + "access_type": "ALL", + "loops": 1, + "r_loops": 1, + "rows": 1000, + "r_rows": 1000, + "cost": "REPLACED", + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 10, + "r_filtered": 10, + "attached_condition": "t11.a < 100 and t11.b is not null" + } + }, + { + "block-nl-join": { + "table": { + "table_name": "t10", + "access_type": "hash_ALL", + "key": "#hash#$hj", + "key_length": "5", + "used_key_parts": ["b"], + "ref": ["test.t11.b"], + "loops": 100, + "r_loops": 1, + "rows": 1000, + "r_rows": 1000, + "cost": "REPLACED", + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 0.209580004, + "r_filtered": 70, + "attached_condition": "t10.a < 700" + }, + "buffer_type": "flat", + "buffer_size": "3Kb", + "join_type": "BNLH", + "attached_condition": "t10.b = t11.b", + "r_loops": 100, + "r_filtered": 100, + "r_unpack_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "r_effective_rows": 2.97 + } + } + ] + } +} +set join_cache_level=@tmp; +drop table t10, t11; |