blob: 8bafb725d89f8a6c035f4529b24322d09fdf392f (
plain)
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
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
|
=====
=== Test ANALYZE behavior after default creation
=====
CREATE TABLE test_ps_flag (a INT) ENGINE=INNODB;
SHOW CREATE TABLE test_ps_flag;
Table Create Table
test_ps_flag CREATE TABLE `test_ps_flag` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
cnt_before
0
SET STATEMENT use_stat_tables=never FOR
ANALYZE TABLE test_ps_flag;
Table Op Msg_type Msg_text
test.test_ps_flag analyze status OK
SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
cnt_after
1
DROP TABLE test_ps_flag;
CREATE TABLE test_ps_flag (a INT) ENGINE=INNODB STATS_PERSISTENT=default;
SHOW CREATE TABLE test_ps_flag;
Table Create Table
test_ps_flag CREATE TABLE `test_ps_flag` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
cnt_before
0
SET STATEMENT use_stat_tables=never FOR
ANALYZE TABLE test_ps_flag;
Table Op Msg_type Msg_text
test.test_ps_flag analyze status OK
SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
cnt_after
1
DROP TABLE test_ps_flag;
=====
=== Test ANALYZE behavior after creation with explicit PS=OFF
=====
CREATE TABLE test_ps_flag (a INT) ENGINE=INNODB STATS_PERSISTENT=0;
SHOW CREATE TABLE test_ps_flag;
Table Create Table
test_ps_flag CREATE TABLE `test_ps_flag` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci STATS_PERSISTENT=0
DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
cnt_before
0
SET STATEMENT use_stat_tables=never FOR
ANALYZE TABLE test_ps_flag;
Table Op Msg_type Msg_text
test.test_ps_flag analyze status OK
SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
cnt_after
0
DROP TABLE test_ps_flag;
=====
=== Test ANALYZE behavior after creation with explicit PS=ON
=====
CREATE TABLE test_ps_flag (a INT) ENGINE=INNODB STATS_PERSISTENT=1;
SHOW CREATE TABLE test_ps_flag;
Table Create Table
test_ps_flag CREATE TABLE `test_ps_flag` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci STATS_PERSISTENT=1
DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
cnt_before
0
SET STATEMENT use_stat_tables=never FOR
ANALYZE TABLE test_ps_flag;
Table Op Msg_type Msg_text
test.test_ps_flag analyze status OK
SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
cnt_after
1
DROP TABLE test_ps_flag;
=====
=== Test ANALYZE behavior after creation with explicit PS=OFF,
=== then ALTER to ON, then ALTER to OFF, then ALTER to default
=====
CREATE TABLE test_ps_flag (a INT) ENGINE=INNODB STATS_PERSISTENT=0;
ALTER TABLE test_ps_flag STATS_PERSISTENT=1;
# restart
SHOW CREATE TABLE test_ps_flag;
Table Create Table
test_ps_flag CREATE TABLE `test_ps_flag` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci STATS_PERSISTENT=1
DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
cnt_before
0
SET STATEMENT use_stat_tables=never FOR
ANALYZE TABLE test_ps_flag;
Table Op Msg_type Msg_text
test.test_ps_flag analyze status OK
SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
cnt_after
1
ALTER TABLE test_ps_flag STATS_PERSISTENT=0;
SHOW CREATE TABLE test_ps_flag;
Table Create Table
test_ps_flag CREATE TABLE `test_ps_flag` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci STATS_PERSISTENT=0
DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
cnt_before
0
SET STATEMENT use_stat_tables=never FOR
ANALYZE TABLE test_ps_flag;
Table Op Msg_type Msg_text
test.test_ps_flag analyze status OK
SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
cnt_after
0
ALTER TABLE test_ps_flag STATS_PERSISTENT=default;
SHOW CREATE TABLE test_ps_flag;
Table Create Table
test_ps_flag CREATE TABLE `test_ps_flag` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
cnt_before
0
SET STATEMENT use_stat_tables=never FOR
ANALYZE TABLE test_ps_flag;
Table Op Msg_type Msg_text
test.test_ps_flag analyze status OK
SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
cnt_after
1
DROP TABLE test_ps_flag;
=====
=== Test ANALYZE behavior after creation with explicit PS=ON,
=== then ALTER to OFF, then ALTER to ON, then ALTER to default
=====
CREATE TABLE test_ps_flag (a INT) ENGINE=INNODB STATS_PERSISTENT=1;
ALTER TABLE test_ps_flag STATS_PERSISTENT=0;
# restart
SHOW CREATE TABLE test_ps_flag;
Table Create Table
test_ps_flag CREATE TABLE `test_ps_flag` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci STATS_PERSISTENT=0
DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
cnt_before
0
SET STATEMENT use_stat_tables=never FOR
ANALYZE TABLE test_ps_flag;
Table Op Msg_type Msg_text
test.test_ps_flag analyze status OK
SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
cnt_after
0
ALTER TABLE test_ps_flag STATS_PERSISTENT=1;
SHOW CREATE TABLE test_ps_flag;
Table Create Table
test_ps_flag CREATE TABLE `test_ps_flag` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci STATS_PERSISTENT=1
DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
cnt_before
0
SET STATEMENT use_stat_tables=never FOR
ANALYZE TABLE test_ps_flag;
Table Op Msg_type Msg_text
test.test_ps_flag analyze status OK
SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
cnt_after
1
ALTER TABLE test_ps_flag STATS_PERSISTENT=default;
SHOW CREATE TABLE test_ps_flag;
Table Create Table
test_ps_flag CREATE TABLE `test_ps_flag` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
cnt_before
0
SET STATEMENT use_stat_tables=never FOR
ANALYZE TABLE test_ps_flag;
Table Op Msg_type Msg_text
test.test_ps_flag analyze status OK
SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
cnt_after
1
DROP TABLE test_ps_flag;
|
