Debian Codegraph @

mysql-test/suite/innodb/t/temporary_table_optimization.test

raw
1 #
2 # WL#6469: Optimizing CREATE/DROP performance for temporary tables
3 #
4 --source include/no_valgrind_without_big.inc
5
6 #########################################################################
7 # #
8 # Will test following scenarios: #
9 # 1. Create/Drop of temp-table. (with and w/o explicit pk) #
10 # 2. Truncate temp-table (result in table drop and recreate). #
11 # 3. Alter of temp-table. #
12 # 4. Import/Discard of temp-table (to check blocked action) #
13 # 5. Renaming of temp-table #
14 # 6. Creating temp-table with large prefix. #
15 # 7. Check Temp table info not stored in I_S datafile and tables #
16 #########################################################################
17
18
19 #-------------------------------------------------------------
20 #
21 # 1. Create/Drop of temp-table. (with and w/o explicit pk) #
22 #
23 create temporary table t1 (i int) engine = innodb;
24 show create table information_schema.innodb_temp_table_info;
25 select count(*) from information_schema.innodb_temp_table_info;
26 insert into t1 values (1), (2), (3), (4);
27 select * from t1;
28 select * from t1 where i = 4;
29 drop table t1;
30 #
31 # recreate table wih same name to ensure entries are removed.
32 create temporary table t1 (i int) engine = innodb;
33 select count(*) from information_schema.innodb_temp_table_info;
34 insert into t1 values (1), (2), (3), (4);
35 select * from t1;
36 select * from t1 where i = 4;
37 drop table t1;
38 #
39 create temporary table t2 (i int) engine = innodb;
40 insert into t2 values (1), (2), (3), (4);
41 select * from t2;
42 select * from t2 where i = 4;
43 select count(*) from information_schema.innodb_temp_table_info;
44 drop table t2;
45 #
46 create temporary table t1 (i int, primary key pk(i)) engine = innodb;
47 create temporary table t2 (i int, primary key pk(i)) engine = innodb;
48 create temporary table t3 (i int, primary key pk(i)) engine = innodb;
49 insert into t1 values (1), (2), (3), (4);
50 insert into t2 values (1), (2), (3), (4);
51 insert into t3 values (1), (2), (3), (4);
52 select * from t1;
53 select * from t1 where i = 4;
54 select count(*) from information_schema.innodb_temp_table_info;
55 drop table t1;
56 select count(*) from information_schema.innodb_temp_table_info;
57 drop table t2;
58 select count(*) from information_schema.innodb_temp_table_info;
59 drop table t3;
60
61 #-------------------------------------------------------------
62 #
63 # 2. Truncate temp-table (result in table drop and recreate). #
64 #
65 create temporary table t1
66 (keyc int, c1 char(100), c2 char(100),
67 primary key(keyc)) engine = innodb;
68 delimiter |;
69 create procedure populate_t1()
70 begin
71 declare i int default 1;
72 while (i <= 200) DO
73 insert into t1 values (i, 'a', 'b');
74 set i = i + 1;
75 end while;
76 end|
77 delimiter ;|
78 set autocommit=0;
79 select * from t1;
80 call populate_t1();
81 select count(*) from t1;
82 select * from t1 limit 10;
83 set autocommit=1;
84 truncate table t1;
85 select * from t1;
86 drop table t1;
87 #
88 # recreate table wih same name to ensure entries are removed.
89 create temporary table t1 (i int) engine = innodb;
90 insert into t1 values (1), (2), (3), (4);
91 select * from t1;
92 select * from t1 where i = 4;
93 drop table t1;
94 #
95 create temporary table t1
96 (keyc int, c1 char(100), c2 char(100),
97 primary key(keyc))
98 engine = innodb;
99 begin;
100 select * from t1;
101 call populate_t1();
102 select count(*) from t1;
103 rollback;
104 select * from t1;
105 begin;
106 call populate_t1();
107 commit;
108 select count(*) from t1;
109 truncate table t1;
110 select * from t1;
111 drop table t1;
112 #
113 drop procedure populate_t1;
114
115
116 #-------------------------------------------------------------
117 #
118 # 3. Alter of temp-table.
119 #
120 create temporary table t1 (t1_i int, t1_f float) engine = innodb;
121 insert into t1 values (1, 1.1), (2, 2.2), (3, 2.2), (4, 4.4);
122 #
123 explain select * from t1 where t1_i = 1;
124 alter table t1 add unique index pri_index(t1_i);
125 explain select * from t1 where t1_i = 1;
126 select * from t1 where t1_i = 1;
127 #
128 --error ER_DUP_ENTRY
129 alter table t1 add unique index sec_index(t1_f);
130 alter table t1 add index sec_index(t1_f);
131 explain select * from t1 where t1_f > 2.2;
132 select * from t1 where t1_f > 2.2;
133 #
134 alter table t1 add column (t1_c char(10));
135 select * from t1;
136 insert into t1 values (5, 5.5, 'krunal');
137 #
138 alter table t1 drop column t1_f;
139 show create table t1;
140 --error ER_BAD_FIELD_ERROR
141 select * from t1 where t1_f > 2.2;
142 #
143 --error ER_ALTER_OPERATION_NOT_SUPPORTED
144 alter table t1 add index sec_index2(t1_c), algorithm=inplace;
145 select count(*) from information_schema.innodb_temp_table_info;
146 #
147 drop table t1;
148
149 #-------------------------------------------------------------
150 #
151 # 4. Import/Discard of temp-table (to check blocked action)
152 #
153 create temporary table t1 (i int, f float) engine = innodb;
154 insert into t1 values (10, 1.1), (20, 2.2);
155 select * from t1;
156 #
157 --error ER_CANNOT_DISCARD_TEMPORARY_TABLE
158 alter table t1 discard tablespace;
159 --error ER_CANNOT_DISCARD_TEMPORARY_TABLE
160 alter table t1 import tablespace;
161 drop table t1;
162
163 #-------------------------------------------------------------
164 #
165 # 5. Renaming of temp-table #
166 #
167 create temporary table t1 (i int) engine=innodb;
168 insert into t1 values (1), (2), (3);
169 select * from t1;
170 #
171 alter table t1 rename t2;
172 --error ER_NO_SUCH_TABLE
173 select * from t1;
174 select * from t2;
175 insert into t2 values (1), (2), (6), (7);
176 select * from t2;
177 drop table t2;
178
179
180 #-------------------------------------------------------------
181 #
182 # 6. Creating temp-table with large prefix. #
183 #
184
185 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
186 # This will lead to warnings on innodb_page_size=8k or 4k:
187 # 8k: Specified key was too long; max key length is 1536 bytes
188 # 4k: Specified key was too long; max key length is 768 bytes
189 --disable_warnings
190 #
191 create temporary table t (
192 a int not null,
193 b blob not null,
194 index sk (b(3021))
195 ) row_format = dynamic engine=innodb;
196 drop table t;
197 #
198 create temporary table t (
199 a int not null,
200 b blob not null,
201 index sk (b(3021))
202 ) row_format = dynamic engine=innodb;
203 drop table t;
204 #
205 create temporary table t (
206 a int not null,
207 b blob not null,
208 index sk (b(3021))
209 ) row_format = dynamic engine=innodb;
210 drop table t;
211 #
212 # Enabling the default 'strict' mode since as part of fix for bug#26848813, an
213 # error is reported when index> 767 bytes and in non-strict mode only a warning
214 # is reported
215 SET sql_mode = 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';
216 SET innodb_strict_mode=OFF;
217 --error ER_TOO_LONG_KEY
218 create temporary table t (
219 a int not null,
220 b blob not null,
221 index sk (b(3021))
222 ) row_format = compact engine=innodb;
223
224 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
225 #
226 create temporary table t (
227 a int not null,
228 b blob not null,
229 index sk (b(3021))
230 ) row_format = dynamic engine=innodb;
231 drop table t;
232 #
233 create temporary table t (
234 a int not null,
235 b blob not null,
236 index sk (b(3021))
237 ) row_format = compressed engine=innodb;
238 drop table t;
239 #
240 #
241 # Enabling the default 'strict' mode since as part of fix for bug#26848813, an
242 # error is reported when index> 767 bytes and in non-strict mode only a warning
243 # is reported
244 SET sql_mode = 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';
245 --error ER_TOO_LONG_KEY
246 create temporary table t (
247 a int not null,
248 b blob not null,
249 index sk (b(3021))
250 ) row_format = compact engine=innodb;
251 #
252 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
253 create temporary table t (
254 a int not null,
255 b blob not null,
256 index sk (b(3021))
257 ) row_format = dynamic engine=innodb;
258 drop table t;
259 --enable_warnings
260 #
261
262 #-------------------------------------------------------------
263 #
264 # 7. Temp table info not stored in I_S
265 #
266 --disable_warnings
267 DROP TABLE IF EXISTS t1,t2,t3;
268 --enable_warnings
269
270 CREATE TABLE t6469_1 ( i INT ) ENGINE = Innodb;
271 CREATE TEMPORARY TABLE t6469_2 ( i INT ) ENGINE = Innodb;
272 CREATE TEMPORARY table t6469_3 ( i INT ) ENGINE = Innodb ROW_FORMAT=compressed;
273 SELECT count(*) FROM information_schema.files WHERE file_name LIKE '%t6469%';
274 SELECT count(*) FROM information_schema.INNODB_DATAFILES WHERE PATH LIKE '%t6469%';
275 SELECT count(*) FROM information_schema.INNODB_TABLES WHERE NAME LIKE '%t6469%';
276
277 DROP TABLE t6469_1,t6469_2,t6469_3;
278 SELECT * FROM information_schema.files WHERE file_name LIKE '%t6469%';
279 SELECT * FROM information_schema.INNODB_DATAFILES WHERE PATH LIKE '%t6469%';
280 SELECT TABLE_ID, NAME, FLAG, N_COLS, SPACE, ROW_FORMAT, ZIP_PAGE_SIZE, SPACE_TYPE,
281 INSTANT_COLS, TOTAL_ROW_VERSIONS FROM information_schema.INNODB_TABLES WHERE NAME LIKE '%t6469%';
282
283 #-------------------------------------------------------------
284 #
285 # remove test-bed.
286 #
287 SET sql_mode=default;