1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
|
SET @old_log_output= @@global.log_output;
SET @old_slow_query_log= @@global.slow_query_log;
SET @old_long_query_time= @@session.long_query_time;
SET @old_log_slow_disable_statements= @@global.log_slow_disabled_statements;
SET @@session.log_slow_verbosity="explain,innodb,query_plan";
CREATE TABLE t1 (i INT PRIMARY KEY AUTO_INCREMENT, j blob) ENGINE=MyISAM;
insert into t1 (j) values ('a'),('b'),('c'),('d');
create table t2 (a int auto_increment primary key) engine=myisam;
SET GLOBAL log_output = 'file,table';
SET GLOBAL slow_query_log = on;
CREATE PROCEDURE slow()
BEGIN
SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b';
SELECT count(*) from t1 where j>'a';
insert into t2 () values();
END
$
CREATE PROCEDURE slow2()
BEGIN
SELECT j,count(*) from t1 group by j;
create temporary table t3 (a int);
alter table t3 add column (b int);
call slow();
drop temporary table t3;
SELECT j,count(*)+1 from t1 group by j,i;
END
$
SET SESSION long_query_time = 0;
SELECT @@log_slow_disabled_statements;
@@log_slow_disabled_statements
sp
TRUNCATE TABLE mysql.slow_log;
ALTER TABLE t1 add column extra int;
CALL slow2();
j count(*)
a 1
b 1
c 1
d 1
count(if(sleep(1) >= 0,0,NULL))
2
count(*)
3
j count(*)+1
a 2
b 2
c 2
d 2
SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part2';
count(if(sleep(1) >= 0,0,NULL))
2
-->
SELECT sql_text FROM mysql.slow_log where sql_text <> "Close stmt" and sql_text <> "Prepare";
sql_text
TRUNCATE TABLE mysql.slow_log
ALTER TABLE t1 add column extra int
CALL slow2()
SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part2'
<--
SET SESSION log_slow_disabled_statements="call,admin";
TRUNCATE TABLE mysql.slow_log;
ALTER TABLE t1 add column extra2 int;
CALL slow2();
j count(*)
a 1
b 1
c 1
d 1
count(if(sleep(1) >= 0,0,NULL))
2
count(*)
3
j count(*)+1
a 2
b 2
c 2
d 2
SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part3';
count(if(sleep(1) >= 0,0,NULL))
2
-->
SELECT sql_text FROM mysql.slow_log where sql_text <> "Close stmt" and sql_text <> "Prepare";
sql_text
TRUNCATE TABLE mysql.slow_log
SELECT j,count(*) from t1 group by j
create temporary table t3 (a int)
SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b'
SELECT count(*) from t1 where j>'a'
insert into t2 () values()
drop temporary table t3
SELECT j,count(*)+1 from t1 group by j,i
SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part3'
<--
SET SESSION log_slow_disabled_statements="";
TRUNCATE TABLE mysql.slow_log;
ALTER TABLE t1 add column extra3 int;
CALL slow2();
j count(*)
a 1
b 1
c 1
d 1
count(if(sleep(1) >= 0,0,NULL))
2
count(*)
3
j count(*)+1
a 2
b 2
c 2
d 2
SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part4';
count(if(sleep(1) >= 0,0,NULL))
2
-->
SELECT sql_text FROM mysql.slow_log where sql_text <> "Close stmt" and sql_text <> "Prepare";
sql_text
TRUNCATE TABLE mysql.slow_log
ALTER TABLE t1 add column extra3 int
SELECT j,count(*) from t1 group by j
create temporary table t3 (a int)
alter table t3 add column (b int)
SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b'
SELECT count(*) from t1 where j>'a'
insert into t2 () values()
call slow()
drop temporary table t3
SELECT j,count(*)+1 from t1 group by j,i
CALL slow2()
SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part4'
<--
SET SESSION log_slow_disabled_statements="call,admin,slave,sp";
TRUNCATE TABLE mysql.slow_log;
ALTER TABLE t1 add column extra4 int;
CALL slow2();
j count(*)
a 1
b 1
c 1
d 1
count(if(sleep(1) >= 0,0,NULL))
2
count(*)
3
j count(*)+1
a 2
b 2
c 2
d 2
SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part5';
count(if(sleep(1) >= 0,0,NULL))
2
-->
SELECT sql_text FROM mysql.slow_log where sql_text <> "Close stmt" and sql_text <> "Prepare";
sql_text
TRUNCATE TABLE mysql.slow_log
SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part5'
<--
DROP TABLE t1,t2;
DROP PROCEDURE slow;
DROP PROCEDURE slow2;
SET @@session.long_query_time= @old_long_query_time;
TRUNCATE TABLE mysql.slow_log;
SET @@global.log_output= @old_log_output;
SET @@global.slow_query_log= @old_slow_query_log;
SET @@global.log_slow_disabled_statements= @old_log_slow_disable_statements;
|
