diff options
Diffstat (limited to 'mysql-test/suite/perfschema/r/batch_table_io_func.result')
| -rw-r--r-- | mysql-test/suite/perfschema/r/batch_table_io_func.result | 195 |
1 files changed, 195 insertions, 0 deletions
diff --git a/mysql-test/suite/perfschema/r/batch_table_io_func.result b/mysql-test/suite/perfschema/r/batch_table_io_func.result new file mode 100644 index 00000000000..212c08c7e90 --- /dev/null +++ b/mysql-test/suite/perfschema/r/batch_table_io_func.result @@ -0,0 +1,195 @@ +UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'YES'; +UPDATE performance_schema.setup_instruments SET enabled = 'YES' +WHERE name in ('wait/io/table/sql/handler', +'wait/lock/table/sql/handler'); +drop procedure if exists before_payload; +drop procedure if exists after_payload; +create procedure before_payload() +begin +TRUNCATE TABLE performance_schema.table_io_waits_summary_by_index_usage; +TRUNCATE TABLE performance_schema.table_io_waits_summary_by_table; +TRUNCATE TABLE performance_schema.events_waits_history_long; +TRUNCATE TABLE performance_schema.events_waits_history; +TRUNCATE TABLE performance_schema.events_waits_current; +end +$$ +create procedure after_payload() +begin +select count(1) as number_seen, +OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, +OPERATION, NUMBER_OF_BYTES +from performance_schema.events_waits_history_long +where OBJECT_SCHEMA = "test" + group by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, OPERATION, NUMBER_OF_BYTES; +select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, +COUNT_STAR, COUNT_READ, COUNT_WRITE +from performance_schema.table_io_waits_summary_by_index_usage +where OBJECT_SCHEMA = "test" + order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME; +select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, +COUNT_STAR, COUNT_READ, COUNT_WRITE +from performance_schema.table_io_waits_summary_by_table +where OBJECT_SCHEMA = "test" + order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME; +end +$$ +drop table if exists t1; +drop table if exists t2; +drop table if exists t3; +create table t0(v int); +create table t1(id1 int, a int); +create table t2(id1 int, id2 int, b int); +create table t3(id2 int, id3 int, c int); +insert into t0 values +(0), (1), (2), (3), (4), +(5), (6), (7), (8), (9); +insert into t1(id1, a) +select v, 100*v from t0; +insert into t2(id1, id2, b) +select X.v, 10*X.v + Y.v, 100*X.v + 10*Y.v +from t0 X, t0 Y; +insert into t3(id2, id3, c) +select 10*X.v + Y.v, 100*X.v + 10*Y.v + Z.v, 100*X.v + 10*Y.v + Z.v +from t0 X, t0 Y, t0 Z; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +analyze table t3; +Table Op Msg_type Msg_text +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK +select * from t1 order by a; +id1 a +0 0 +1 100 +2 200 +3 300 +4 400 +5 500 +6 600 +7 700 +8 800 +9 900 +select * from t2 +where (b >= 180) and (b <= 220) +order by b; +id1 id2 b +1 18 180 +1 19 190 +2 20 200 +2 21 210 +2 22 220 +select * from t3 +where (c >= 587) and (c <= 612) +order by c; +id2 id3 c +58 587 587 +58 588 588 +58 589 589 +59 590 590 +59 591 591 +59 592 592 +59 593 593 +59 594 594 +59 595 595 +59 596 596 +59 597 597 +59 598 598 +59 599 599 +60 600 600 +60 601 601 +60 602 602 +60 603 603 +60 604 604 +60 605 605 +60 606 606 +60 607 607 +60 608 608 +60 609 609 +61 610 610 +61 611 611 +61 612 612 +explain extended select t1.*, t2.*, t3.* +from t1 join t2 using (id1) join t3 using (id2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`id1` AS `id1`,`test`.`t2`.`id2` AS `id2`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`id2` AS `id2`,`test`.`t3`.`id3` AS `id3`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t3`.`id2` = `test`.`t2`.`id2` and `test`.`t2`.`id1` = `test`.`t1`.`id1` +call before_payload(); +select t1.*, t2.*, t3.* +from t1 join t2 using (id1) join t3 using (id2); +call after_payload(); +number_seen OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME INDEX_NAME OPERATION NUMBER_OF_BYTES +11 TABLE test t1 NULL fetch 1 +1 TABLE test t1 NULL read external NULL +1 TABLE test t1 NULL read normal NULL +101 TABLE test t2 NULL fetch 1 +1 TABLE test t2 NULL read external NULL +1 TABLE test t2 NULL read normal NULL +1 TABLE test t3 NULL fetch 1000 +1 TABLE test t3 NULL read external NULL +1 TABLE test t3 NULL read normal NULL +OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME INDEX_NAME COUNT_STAR COUNT_READ COUNT_WRITE +TABLE test t0 NULL 0 0 0 +TABLE test t1 NULL 11 11 0 +TABLE test t2 NULL 101 101 0 +TABLE test t3 NULL 1000 1000 0 +OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_READ COUNT_WRITE +TABLE test t0 0 0 0 +TABLE test t1 11 11 0 +TABLE test t2 101 101 0 +TABLE test t3 1000 1000 0 +alter table t1 add unique index(id1); +alter table t2 add unique index(id2); +alter table t2 add index(id1); +alter table t3 add unique index(id3); +alter table t3 add index(id2); +explain extended select t1.*, t2.*, t3.* +from t1 join t2 using (id1) join t3 using (id2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL id1 NULL NULL NULL 10 100.00 Using where +1 SIMPLE t2 ref id2,id1 id1 5 test.t1.id1 10 100.00 Using where +1 SIMPLE t3 ref id2 id2 5 test.t2.id2 10 100.00 +Warnings: +Note 1003 select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`id1` AS `id1`,`test`.`t2`.`id2` AS `id2`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`id2` AS `id2`,`test`.`t3`.`id3` AS `id3`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t3`.`id2` = `test`.`t2`.`id2` and `test`.`t2`.`id1` = `test`.`t1`.`id1` +call before_payload(); +select t1.*, t2.*, t3.* +from t1 join t2 using (id1) join t3 using (id2); +call after_payload(); +number_seen OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME INDEX_NAME OPERATION NUMBER_OF_BYTES +11 TABLE test t1 NULL fetch 1 +1 TABLE test t1 id1 read external NULL +1 TABLE test t1 id1 read normal NULL +110 TABLE test t2 id1 fetch 1 +1 TABLE test t2 id2 read external NULL +1 TABLE test t2 id2 read normal NULL +100 TABLE test t3 id2 fetch 10 +1 TABLE test t3 id3 read external NULL +1 TABLE test t3 id3 read normal NULL +OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME INDEX_NAME COUNT_STAR COUNT_READ COUNT_WRITE +TABLE test t0 NULL 0 0 0 +TABLE test t1 NULL 11 11 0 +TABLE test t1 id1 0 0 0 +TABLE test t2 id1 110 110 0 +TABLE test t2 id2 0 0 0 +TABLE test t3 id2 1000 1000 0 +TABLE test t3 id3 0 0 0 +OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_READ COUNT_WRITE +TABLE test t0 0 0 0 +TABLE test t1 11 11 0 +TABLE test t2 110 110 0 +TABLE test t3 1000 1000 0 +drop table t0; +drop table t1; +drop table t2; +drop table t3; +drop procedure before_payload; +drop procedure after_payload; +UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES'; |