Debian Codegraph @

mysql-test/suite/perfschema/r/batch_table_io_func.result

raw
1 UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'YES';
2 UPDATE performance_schema.setup_instruments SET enabled = 'YES'
3 WHERE name in ('wait/io/table/sql/handler',
4 'wait/lock/table/sql/handler');
5 drop procedure if exists before_payload;
6 drop procedure if exists after_payload;
7 create procedure before_payload()
8 begin
9 TRUNCATE TABLE performance_schema.table_io_waits_summary_by_index_usage;
10 TRUNCATE TABLE performance_schema.table_io_waits_summary_by_table;
11 TRUNCATE TABLE performance_schema.events_waits_history_long;
12 TRUNCATE TABLE performance_schema.events_waits_history;
13 TRUNCATE TABLE performance_schema.events_waits_current;
14 end
15 $$
16 create procedure after_payload()
17 begin
18 select count(1) as number_seen,
19 OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
20 OPERATION, NUMBER_OF_BYTES
21 from performance_schema.events_waits_history_long
22 where OBJECT_SCHEMA = "test"
23 group by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, OPERATION, NUMBER_OF_BYTES;
24 select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
25 COUNT_STAR, COUNT_READ, COUNT_WRITE
26 from performance_schema.table_io_waits_summary_by_index_usage
27 where OBJECT_SCHEMA = "test"
28 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME;
29 select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME,
30 COUNT_STAR, COUNT_READ, COUNT_WRITE
31 from performance_schema.table_io_waits_summary_by_table
32 where OBJECT_SCHEMA = "test"
33 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME;
34 end
35 $$
36 drop table if exists t1;
37 drop table if exists t2;
38 drop table if exists t3;
39 create table t0(v int);
40 create table t1(id1 int, a int);
41 create table t2(id1 int, id2 int, b int);
42 create table t3(id2 int, id3 int, c int);
43 insert into t0 values
44 (0), (1), (2), (3), (4),
45 (5), (6), (7), (8), (9);
46 insert into t1(id1, a)
47 select v, 100*v from t0;
48 insert into t2(id1, id2, b)
49 select X.v, 10*X.v + Y.v, 100*X.v + 10*Y.v
50 from t0 X, t0 Y;
51 insert into t3(id2, id3, c)
52 select 10*X.v + Y.v, 100*X.v + 10*Y.v + Z.v, 100*X.v + 10*Y.v + Z.v
53 from t0 X, t0 Y, t0 Z;
54 analyze table t1;
55 Table Op Msg_type Msg_text
56 test.t1 analyze status Engine-independent statistics collected
57 test.t1 analyze status OK
58 analyze table t2;
59 Table Op Msg_type Msg_text
60 test.t2 analyze status Engine-independent statistics collected
61 test.t2 analyze status OK
62 analyze table t3;
63 Table Op Msg_type Msg_text
64 test.t3 analyze status Engine-independent statistics collected
65 test.t3 analyze status OK
66 select * from t1 order by a;
67 id1 a
68 0 0
69 1 100
70 2 200
71 3 300
72 4 400
73 5 500
74 6 600
75 7 700
76 8 800
77 9 900
78 select * from t2
79 where (b >= 180) and (b <= 220)
80 order by b;
81 id1 id2 b
82 1 18 180
83 1 19 190
84 2 20 200
85 2 21 210
86 2 22 220
87 select * from t3
88 where (c >= 587) and (c <= 612)
89 order by c;
90 id2 id3 c
91 58 587 587
92 58 588 588
93 58 589 589
94 59 590 590
95 59 591 591
96 59 592 592
97 59 593 593
98 59 594 594
99 59 595 595
100 59 596 596
101 59 597 597
102 59 598 598
103 59 599 599
104 60 600 600
105 60 601 601
106 60 602 602
107 60 603 603
108 60 604 604
109 60 605 605
110 60 606 606
111 60 607 607
112 60 608 608
113 60 609 609
114 61 610 610
115 61 611 611
116 61 612 612
117 explain extended select t1.*, t2.*, t3.*
118 from t1 join t2 using (id1) join t3 using (id2);
119 id select_type table type possible_keys key key_len ref rows filtered Extra
120 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00
121 1 SIMPLE t2 ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer (flat, BNL join)
122 1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 100.00 Using where; Using join buffer (incremental, BNL join)
123 Warnings:
124 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`
125 call before_payload();
126 select t1.*, t2.*, t3.*
127 from t1 join t2 using (id1) join t3 using (id2);
128 call after_payload();
129 number_seen OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME INDEX_NAME OPERATION NUMBER_OF_BYTES
130 11 TABLE test t1 NULL fetch 1
131 1 TABLE test t1 NULL read external NULL
132 1 TABLE test t1 NULL read normal NULL
133 101 TABLE test t2 NULL fetch 1
134 1 TABLE test t2 NULL read external NULL
135 1 TABLE test t2 NULL read normal NULL
136 1 TABLE test t3 NULL fetch 1000
137 1 TABLE test t3 NULL read external NULL
138 1 TABLE test t3 NULL read normal NULL
139 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME INDEX_NAME COUNT_STAR COUNT_READ COUNT_WRITE
140 TABLE test t0 NULL 0 0 0
141 TABLE test t1 NULL 11 11 0
142 TABLE test t2 NULL 101 101 0
143 TABLE test t3 NULL 1000 1000 0
144 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_READ COUNT_WRITE
145 TABLE test t0 0 0 0
146 TABLE test t1 11 11 0
147 TABLE test t2 101 101 0
148 TABLE test t3 1000 1000 0
149 alter table t1 add unique index(id1);
150 alter table t2 add unique index(id2);
151 alter table t2 add index(id1);
152 alter table t3 add unique index(id3);
153 alter table t3 add index(id2);
154 explain extended select t1.*, t2.*, t3.*
155 from t1 join t2 using (id1) join t3 using (id2);
156 id select_type table type possible_keys key key_len ref rows filtered Extra
157 1 SIMPLE t1 ALL id1 NULL NULL NULL 10 100.00
158 1 SIMPLE t2 ALL id2,id1 NULL NULL NULL 100 10.00 Using where; Using join buffer (flat, BNL join)
159 1 SIMPLE t3 ref id2 id2 5 test.t2.id2 10 100.00
160 Warnings:
161 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`
162 call before_payload();
163 select t1.*, t2.*, t3.*
164 from t1 join t2 using (id1) join t3 using (id2);
165 call after_payload();
166 number_seen OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME INDEX_NAME OPERATION NUMBER_OF_BYTES
167 11 TABLE test t1 NULL fetch 1
168 1 TABLE test t1 id1 read external NULL
169 1 TABLE test t1 id1 read normal NULL
170 101 TABLE test t2 NULL fetch 1
171 1 TABLE test t2 id2 read external NULL
172 1 TABLE test t2 id2 read normal NULL
173 100 TABLE test t3 id2 fetch 10
174 1 TABLE test t3 id3 read external NULL
175 1 TABLE test t3 id3 read normal NULL
176 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME INDEX_NAME COUNT_STAR COUNT_READ COUNT_WRITE
177 TABLE test t0 NULL 0 0 0
178 TABLE test t1 NULL 11 11 0
179 TABLE test t1 id1 0 0 0
180 TABLE test t2 NULL 101 101 0
181 TABLE test t2 id1 0 0 0
182 TABLE test t2 id2 0 0 0
183 TABLE test t3 id2 1000 1000 0
184 TABLE test t3 id3 0 0 0
185 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_READ COUNT_WRITE
186 TABLE test t0 0 0 0
187 TABLE test t1 11 11 0
188 TABLE test t2 101 101 0
189 TABLE test t3 1000 1000 0
190 drop table t0;
191 drop table t1;
192 drop table t2;
193 drop table t3;
194 drop procedure before_payload;
195 drop procedure after_payload;
196 UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';