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 --source include/have_innodb.inc
6
7 #########################################################################
8 # #
9 # Will test following scenarios: #
10 # 1. Create/Drop of temp-table. (with and w/o explicit pk) #
11 # 2. Truncate temp-table (result in table drop and recreate). #
12 # 3. Alter of temp-table. #
13 # 4. Import/Discard of temp-table (to check blocked action) #
14 # 5. Renaming of temp-table #
15 # 6. Creating temp-table with large prefix. #
16 # 7. Check Temp table info not stored in InnoDB system tables #
17 #########################################################################
18
19
20 #-------------------------------------------------------------
21 #
22 # 1. Create/Drop of temp-table. (with and w/o explicit pk) #
23 #
24 create temporary table t1 (i int) engine = innodb;
25 insert into t1 values (1), (2), (3), (4);
26 select * from t1;
27 select * from t1 where i = 4;
28 drop table t1;
29 #
30 # recreate table wih same name to ensure entries are removed.
31 create temporary table t1 (i int) engine = innodb;
32 insert into t1 values (1), (2), (3), (4);
33 select * from t1;
34 select * from t1 where i = 4;
35 drop table t1;
36 #
37 create temporary table t2 (i int) engine = innodb;
38 insert into t2 values (1), (2), (3), (4);
39 select * from t2;
40 select * from t2 where i = 4;
41 drop table t2;
42
43 #-------------------------------------------------------------
44 #
45 # 2. Truncate temp-table (result in table drop and recreate). #
46 #
47 create temporary table t1
48 (keyc int, c1 char(100), c2 char(100),
49 primary key(keyc)) engine = innodb;
50 delimiter |;
51 create procedure populate_t1()
52 begin
53 declare i int default 1;
54 while (i <= 200) DO
55 insert into t1 values (i, 'a', 'b');
56 set i = i + 1;
57 end while;
58 end|
59 delimiter ;|
60 set autocommit=0;
61 select count(*) from t1;
62 call populate_t1();
63 select count(*) from t1;
64 select * from t1 limit 10;
65 set autocommit=1;
66 truncate table t1;
67 select count(*) from t1;
68 drop table t1;
69 #
70 # recreate table wih same name to ensure entries are removed.
71 create temporary table t1 (i int) engine = innodb;
72 insert into t1 values (1), (2), (3), (4);
73 select * from t1;
74 select * from t1 where i = 4;
75 drop table t1;
76 #
77 create temporary table t1
78 (keyc int, c1 char(100), c2 char(100),
79 primary key(keyc))
80 engine = innodb;
81 begin;
82 select count(*) from t1;
83 call populate_t1();
84 select count(*) from t1;
85 rollback;
86 select count(*) from t1;
87 begin;
88 call populate_t1();
89 commit;
90 select count(*) from t1;
91 truncate table t1;
92 select count(*) from t1;
93 drop table t1;
94 #
95 drop procedure populate_t1;
96
97
98 #-------------------------------------------------------------
99 #
100 # 3. Alter of temp-table.
101 #
102 create temporary table t1 (t1_i int, t1_f float) engine = innodb;
103 insert into t1 values (1, 1.1), (2, 2.5), (3, 2.5), (4, 4.4);
104 #
105 explain select * from t1 where t1_i = 1;
106 alter table t1 add unique index pri_index(t1_i);
107 explain select * from t1 where t1_i = 1;
108 select * from t1 where t1_i = 1;
109 #
110 --error ER_DUP_ENTRY
111 alter table t1 add unique index sec_index(t1_f);
112 alter table t1 add index sec_index(t1_f);
113 explain select * from t1 where t1_f >= 2.5;
114 select * from t1 where t1_f >= 2.5;
115 #
116 alter table t1 add column (t1_c char(10));
117 select * from t1;
118 insert into t1 values (5, 5.5, 'krunal');
119 #
120 alter table t1 drop column t1_f;
121 show create table t1;
122 --error ER_BAD_FIELD_ERROR
123 select * from t1 where t1_f >= 2.5;
124 #
125 --error ER_ALTER_OPERATION_NOT_SUPPORTED
126 alter table t1 add index sec_index2(t1_c), algorithm=inplace;
127 #
128 drop table t1;
129
130 #-------------------------------------------------------------
131 #
132 # 4. Import/Discard of temp-table (to check blocked action)
133 #
134 create temporary table t1 (i int, f float) engine = innodb;
135 insert into t1 values (10, 1.1), (20, 2.2);
136 select * from t1;
137 #
138 --error ER_CANNOT_DISCARD_TEMPORARY_TABLE
139 alter table t1 discard tablespace;
140 --error ER_CANNOT_DISCARD_TEMPORARY_TABLE
141 alter table t1 import tablespace;
142 drop table t1;
143
144 #-------------------------------------------------------------
145 #
146 # 5. Renaming of temp-table #
147 #
148 create temporary table t1 (i int) engine=innodb;
149 insert into t1 values (1), (2), (3);
150 select * from t1;
151 #
152 alter table t1 rename t2;
153 --error ER_NO_SUCH_TABLE
154 select * from t1;
155 select * from t2;
156 insert into t2 values (1), (2), (6), (7);
157 select * from t2;
158 drop table t2;
159
160
161 #-------------------------------------------------------------
162 #
163 # 6. Creating temp-table with large prefix. #
164 #
165 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
166 # This will lead to warnings on innodb_page_size=8k or 4k:
167 # 8k: Specified key was too long; max key length is 1536 bytes
168 # 4k: Specified key was too long; max key length is 768 bytes
169 --disable_warnings
170 #
171 create temporary table t (
172 a int not null,
173 b blob not null,
174 index sk (b(3021))
175 ) row_format = dynamic engine=innodb;
176 drop table t;
177 #
178 create temporary table t (
179 a int not null,
180 b blob not null,
181 index sk (b(3021))
182 ) row_format = dynamic engine=innodb;
183 drop table t;
184 #
185 create temporary table t (
186 a int not null,
187 b blob not null,
188 index sk (b(3021))
189 ) row_format = dynamic engine=innodb;
190 drop table t;
191 #
192 SET innodb_strict_mode=OFF;
193 --error ER_INDEX_COLUMN_TOO_LONG
194 create temporary table t (
195 a int not null,
196 b blob not null,
197 index sk (b(3021))
198 ) row_format = compact engine=innodb;
199 #
200 create temporary table t (
201 a int not null,
202 b blob not null,
203 index sk (b(3021))
204 ) row_format = dynamic engine=innodb;
205 drop table t;
206 #
207 create temporary table t (
208 a int not null,
209 b blob not null,
210 index sk (b(3021))
211 ) row_format = compressed engine=innodb;
212 drop table t;
213 #
214 --error ER_INDEX_COLUMN_TOO_LONG
215 create temporary table t (
216 a int not null,
217 b blob not null,
218 index sk (b(3021))
219 ) row_format = compact engine=innodb;
220 #
221 create temporary table t (
222 a int not null,
223 b blob not null,
224 index sk (b(3021))
225 ) row_format = dynamic engine=innodb;
226 drop table t;
227 --enable_warnings
228 #
229
230 #-------------------------------------------------------------
231 #
232 # 7. Temp table info not stored in I_S
233 #
234 CREATE TABLE t1 ( i INT ) ENGINE = Innodb;
235 CREATE TEMPORARY TABLE t2 ( i INT ) ENGINE = Innodb;
236 SELECT COUNT(*) FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE '%test%t_';
237 CREATE TEMPORARY table t3 ( i INT ) ENGINE = Innodb;
238 SELECT COUNT(*) FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE '%test%t_';
239 DROP TABLE t1,t2,t3;
240 SELECT COUNT(*) FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE '%test%t_';