diff options
Diffstat (limited to 'mysql-test/r/analyze_format_json.result')
| -rw-r--r-- | mysql-test/r/analyze_format_json.result | 172 |
1 files changed, 172 insertions, 0 deletions
diff --git a/mysql-test/r/analyze_format_json.result b/mysql-test/r/analyze_format_json.result new file mode 100644 index 00000000000..53fdf3ef3d9 --- /dev/null +++ b/mysql-test/r/analyze_format_json.result @@ -0,0 +1,172 @@ +drop table if exists t0,t1,t2,t3; +create table t0 (a int); +INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +# r_filtered=30%, because 3 rows match: 0,1,2 +analyze format=json select * from t0 where a<3; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t0", + "access_type": "ALL", + "r_loops": 1, + "rows": 10, + "r_rows": 10, + "filtered": 100, + "r_filtered": 30, + "attached_condition": "(t0.a < 3)" + } + } +} +create table t1 (a int, b int, c int, key(a)); +insert into t1 select A.a*10 + B.a, A.a*10 + B.a, A.a*10 + B.a from t0 A, t0 B; +analyze +select * from t0, t1 where t1.a=t0.a and t0.a > 9; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 10 100.00 0.00 Using where +1 SIMPLE t1 ref a a 5 test.t0.a 1 NULL 100.00 NULL +analyze format=json +select * from t0, t1 where t1.a=t0.a and t0.a > 9; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t0", + "access_type": "ALL", + "r_loops": 1, + "rows": 10, + "r_rows": 10, + "filtered": 100, + "r_filtered": 0, + "attached_condition": "((t0.a > 9) and (t0.a is not null))" + }, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t0.a"], + "r_loops": 0, + "rows": 1, + "r_rows": null, + "filtered": 100, + "r_filtered": null + } + } +} +analyze +select * from t0, t1 where t1.a=t0.a and t1.b<4; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 10 100.00 100.00 Using where +1 SIMPLE t1 ref a a 5 test.t0.a 1 1 100.00 40.00 Using where +analyze format=json +select * from t0, t1 where t1.a=t0.a and t1.b<4; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t0", + "access_type": "ALL", + "r_loops": 1, + "rows": 10, + "r_rows": 10, + "filtered": 100, + "r_filtered": 100, + "attached_condition": "(t0.a is not null)" + }, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t0.a"], + "r_loops": 10, + "rows": 1, + "r_rows": 1, + "filtered": 100, + "r_filtered": 40, + "attached_condition": "(t1.b < 4)" + } + } +} +analyze +select * from t1 A, t1 B where A.b<2 and B.b>5; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE A ALL NULL NULL NULL NULL 100 100 100.00 2.00 Using where +1 SIMPLE B ALL NULL NULL NULL NULL 100 100 100.00 94.00 Using where; Using join buffer (flat, BNL join) +analyze format=json +select * from t1 A, t1 B where A.b<20 and B.b<60; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "A", + "access_type": "ALL", + "r_loops": 1, + "rows": 100, + "r_rows": 100, + "filtered": 100, + "r_filtered": 20, + "attached_condition": "(A.b < 20)" + }, + "block-nl-join": { + "table": { + "table_name": "B", + "access_type": "ALL", + "r_loops": 1, + "rows": 100, + "r_rows": 100, + "filtered": 100, + "r_filtered": 60, + "attached_condition": "(B.b < 60)" + }, + "buffer_type": "flat", + "join_type": "BNL", + "r_filtered": 100 + } + } +} +analyze format=json +select * from t1 A, t1 B where A.b<20 and B.b<60 and A.c > B.c; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "A", + "access_type": "ALL", + "r_loops": 1, + "rows": 100, + "r_rows": 100, + "filtered": 100, + "r_filtered": 20, + "attached_condition": "(A.b < 20)" + }, + "block-nl-join": { + "table": { + "table_name": "B", + "access_type": "ALL", + "r_loops": 1, + "rows": 100, + "r_rows": 100, + "filtered": 100, + "r_filtered": 60, + "attached_condition": "(B.b < 60)" + }, + "buffer_type": "flat", + "join_type": "BNL", + "attached_condition": "(A.c > B.c)", + "r_filtered": 15.833 + } + } +} +drop table t1; +drop table t0; |