1
drop table if exists t1,t2;
2
create temporary table t1 (a int not null,b int not null, primary key using HASH (a)) engine=MEMORY comment="testing heaps";
3
insert into t1 values(1,1),(2,2),(3,3),(4,4);
4
delete from t1 where a=1 or a=0;
5
show table status like "t1";
6
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
7
# test t1 TEMPORARY MEMORY # # # # #
9
Table Unique Key_name Seq_in_index Column_name
16
select * from t1 where a=4;
19
update t1 set b=5 where a=4;
20
update t1 set b=b+1 where a>=3;
21
replace t1 values (3,3);
27
alter table t1 add c int not null, add key using HASH (c,a);
29
create temporary table t1 (a int not null,b int not null, primary key using HASH (a)) engine=MEMORY comment="testing heaps";
30
insert into t1 values(1,1),(2,2),(3,3),(4,4);
31
delete from t1 where a > 0;
35
create temporary table t1 (a int not null,b int not null, primary key using HASH (a)) engine=MEMORY comment="testing heaps";
36
insert into t1 values(1,1),(2,2),(3,3),(4,4);
37
alter table t1 modify a int not null auto_increment, engine=innodb, comment="new innodb table";
38
show table status like "t1";
39
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
40
# test t1 TEMPORARY InnoDB # # # # #
48
create temporary table t1 (a int not null) engine=MEMORY;
49
insert into t1 values (869751),(736494),(226312),(802616),(728912);
50
select * from t1 where a > 736494;
54
alter table t1 add unique uniq_id using HASH (a);
55
select * from t1 where a > 736494;
59
select * from t1 where a = 736494;
62
select * from t1 where a=869751 or a=736494;
66
select * from t1 where a in (869751,736494,226312,802616);
72
alter table t1 engine=innodb;
73
explain select * from t1 where a in (869751,736494,226312,802616);
74
id select_type table type possible_keys key key_len ref rows Extra
75
1 SIMPLE t1 index uniq_id uniq_id 4 NULL 5 Using where; Using index
77
create temporary table t1 (x int not null, y int not null, key x using HASH (x), unique y using HASH (y))
79
insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6);
80
select * from t1 where x=1;
84
select * from t1,t1 as t2 where t1.x=t2.y;
85
ERROR HY000: Can't reopen table: 't1'
86
explain select * from t1,t1 as t2 where t1.x=t2.y;
87
ERROR HY000: Can't reopen table: 't1'
89
create temporary table t1 (a int) engine=MEMORY;
90
insert into t1 values(1);
91
select max(a) from t1;
95
CREATE TEMPORARY TABLE t1 ( a int not null default 0, b int not null default 0, key using HASH (a), key using HASH (b) ) ENGINE=MEMORY;
96
insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
97
select * from t1 where a=1;
105
insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
106
select * from t1 where a=1;
121
create temporary table t1 (id int not null, primary key using HASH (id)) engine=MEMORY;
122
insert into t1 values(1);
123
select max(id) from t1;
126
insert into t1 values(2);
127
select max(id) from t1;
130
replace into t1 values(1);
132
create temporary table t1 (n int) engine=MEMORY;
134
create temporary table t1 (n int) engine=MEMORY;
135
drop table if exists t1;
136
CREATE TEMPORARY table t1(f1 int not null,f2 char(20) not
137
null,index(f2)) engine=MEMORY;
138
INSERT into t1 set f1=12,f2="bill";
139
INSERT into t1 set f1=13,f2="bill";
140
INSERT into t1 set f1=14,f2="bill";
141
INSERT into t1 set f1=15,f2="bill";
142
INSERT into t1 set f1=16,f2="ted";
143
INSERT into t1 set f1=12,f2="ted";
144
INSERT into t1 set f1=12,f2="ted";
145
INSERT into t1 set f1=12,f2="ted";
146
INSERT into t1 set f1=12,f2="ted";
147
delete from t1 where f2="bill";
156
create temporary table t1 (btn char(10) not null, key using HASH (btn)) engine=MEMORY;
157
insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i");
158
explain select * from t1 where btn like "q%";
159
id select_type table type possible_keys key key_len ref rows Extra
160
1 SIMPLE t1 ALL btn NULL NULL NULL 14 Using where
161
select * from t1 where btn like "q%";
163
alter table t1 add column new_col char(1) not null, add key using HASH (btn,new_col), drop key btn;
164
update t1 set new_col=left(btn,1);
165
explain select * from t1 where btn="a";
166
id select_type table type possible_keys key key_len ref rows Extra
167
1 SIMPLE t1 ALL btn NULL NULL NULL 14 Using where
168
explain select * from t1 where btn="a" and new_col="a";
169
id select_type table type possible_keys key key_len ref rows Extra
170
1 SIMPLE t1 ref btn btn 48 const,const 2 Using where
172
CREATE TEMPORARY TABLE t1 (
175
KEY a using HASH (a),
176
UNIQUE b using HASH (b)
178
INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3);
179
SELECT * FROM t1 WHERE a=NULL;
181
explain SELECT * FROM t1 WHERE a IS NULL;
182
id select_type table type possible_keys key key_len ref rows Extra
183
1 SIMPLE t1 ref a a 5 const 2 Using where
184
SELECT * FROM t1 WHERE a<=>NULL;
187
SELECT * FROM t1 WHERE b=NULL;
189
explain SELECT * FROM t1 WHERE b IS NULL;
190
id select_type table type possible_keys key key_len ref rows Extra
191
1 SIMPLE t1 ref b b 5 const 1 Using where
192
SELECT * FROM t1 WHERE b<=>NULL;
195
INSERT INTO t1 VALUES (1,3);
196
ERROR 23000: Duplicate entry '3' for key 'b'
198
CREATE TEMPORARY TABLE t1 (a int not null, primary key using HASH (a)) engine=MEMORY;
199
INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
200
DELETE from t1 where a < 100;
204
create temporary table t1
211
insert into t1 values ('aaaa', 'prefill-hash=5',0);
212
insert into t1 values ('aaab', 'prefill-hash=0',0);
213
insert into t1 values ('aaac', 'prefill-hash=7',0);
214
insert into t1 values ('aaad', 'prefill-hash=2',0);
215
insert into t1 values ('aaae', 'prefill-hash=1',0);
216
insert into t1 values ('aaaf', 'prefill-hash=4',0);
217
insert into t1 values ('aaag', 'prefill-hash=3',0);
218
insert into t1 values ('aaah', 'prefill-hash=6',0);
219
explain select * from t1 where a='aaaa';
220
id select_type table type possible_keys key key_len ref rows Extra
221
1 SIMPLE t1 ref a a 34 const 2 Using where
222
explain select * from t1 where a='aaab';
223
id select_type table type possible_keys key key_len ref rows Extra
224
1 SIMPLE t1 ref a a 34 const 2 Using where
225
explain select * from t1 where a='aaac';
226
id select_type table type possible_keys key key_len ref rows Extra
227
1 SIMPLE t1 ref a a 34 const 2 Using where
228
explain select * from t1 where a='aaad';
229
id select_type table type possible_keys key key_len ref rows Extra
230
1 SIMPLE t1 ref a a 34 const 2 Using where
231
insert into t1 select * from t1;
232
ERROR HY000: Can't reopen table: 't1'
234
explain select * from t1 where a='aaaa';
235
id select_type table type possible_keys key key_len ref rows Extra
236
1 SIMPLE t1 ref a a 34 const 2 Using where
237
explain select * from t1 where a='aaab';
238
id select_type table type possible_keys key key_len ref rows Extra
239
1 SIMPLE t1 ref a a 34 const 2 Using where
240
explain select * from t1 where a='aaac';
241
id select_type table type possible_keys key key_len ref rows Extra
242
1 SIMPLE t1 ref a a 34 const 2 Using where
243
explain select * from t1 where a='aaad';
244
id select_type table type possible_keys key key_len ref rows Extra
245
1 SIMPLE t1 ref a a 34 const 2 Using where
247
explain select * from t1 where a='aaaa';
248
id select_type table type possible_keys key key_len ref rows Extra
249
1 SIMPLE t1 ref a a 34 const 2 Using where
250
explain select * from t1 where a='aaab';
251
id select_type table type possible_keys key key_len ref rows Extra
252
1 SIMPLE t1 ref a a 34 const 2 Using where
253
explain select * from t1 where a='aaac';
254
id select_type table type possible_keys key key_len ref rows Extra
255
1 SIMPLE t1 ref a a 34 const 2 Using where
256
explain select * from t1 where a='aaad';
257
id select_type table type possible_keys key key_len ref rows Extra
258
1 SIMPLE t1 ref a a 34 const 2 Using where
259
create temporary table t2 as select * from t1;
261
insert into t1 select * from t2;
262
explain select * from t1 where a='aaaa';
263
id select_type table type possible_keys key key_len ref rows Extra
264
1 SIMPLE t1 ref a a 34 const 2 Using where
265
explain select * from t1 where a='aaab';
266
id select_type table type possible_keys key key_len ref rows Extra
267
1 SIMPLE t1 ref a a 34 const 2 Using where
268
explain select * from t1 where a='aaac';
269
id select_type table type possible_keys key key_len ref rows Extra
270
1 SIMPLE t1 ref a a 34 const 2 Using where
271
explain select * from t1 where a='aaad';
272
id select_type table type possible_keys key key_len ref rows Extra
273
1 SIMPLE t1 ref a a 34 const 2 Using where
275
create temporary table t1 (
276
id int not null primary key auto_increment,
277
name varchar(20) not null,
278
index heap_idx(name),
279
index btree_idx using btree(name)
281
create temporary table t2 (
282
id int not null primary key auto_increment,
283
name varchar(20) not null,
284
index btree_idx using btree(name),
287
insert into t1 (name) values ('Matt'), ('Lilu'), ('Corbin'), ('Carly'),
288
('Suzy'), ('Hoppy'), ('Burrito'), ('Mimi'), ('Sherry'), ('Ben'), ('Phil'),
290
insert into t2 select * from t1;
291
explain select * from t1 where name='matt';
292
id select_type table type possible_keys key key_len ref rows Extra
293
1 SIMPLE t1 ref heap_idx,btree_idx btree_idx 82 const 1 Using where
294
explain select * from t2 where name='matt';
295
id select_type table type possible_keys key key_len ref rows Extra
296
1 SIMPLE t2 ref btree_idx,heap_idx btree_idx 82 const 1 Using where
297
explain select * from t1 where name='Lilu';
298
id select_type table type possible_keys key key_len ref rows Extra
299
1 SIMPLE t1 ref heap_idx,btree_idx btree_idx 82 const 1 Using where
300
explain select * from t2 where name='Lilu';
301
id select_type table type possible_keys key key_len ref rows Extra
302
1 SIMPLE t2 ref btree_idx,heap_idx btree_idx 82 const 1 Using where
303
explain select * from t1 where name='Phil';
304
id select_type table type possible_keys key key_len ref rows Extra
305
1 SIMPLE t1 ref heap_idx,btree_idx btree_idx 82 const 1 Using where
306
explain select * from t2 where name='Phil';
307
id select_type table type possible_keys key key_len ref rows Extra
308
1 SIMPLE t2 ref btree_idx,heap_idx btree_idx 82 const 1 Using where
309
explain select * from t1 where name='Lilu';
310
id select_type table type possible_keys key key_len ref rows Extra
311
1 SIMPLE t1 ref heap_idx,btree_idx btree_idx 82 const 1 Using where
312
explain select * from t2 where name='Lilu';
313
id select_type table type possible_keys key key_len ref rows Extra
314
1 SIMPLE t2 ref btree_idx,heap_idx btree_idx 82 const 1 Using where
315
insert into t1 (name) select name from t2;
316
insert into t1 (name) select name from t2;
317
insert into t1 (name) select name from t2;
318
insert into t1 (name) select name from t2;
319
insert into t1 (name) select name from t2;
320
insert into t1 (name) select name from t2;
322
select count(*) from t1 where name='Matt';
325
explain select * from t1 ignore index (btree_idx) where name='matt';
326
id select_type table type possible_keys key key_len ref rows Extra
327
1 SIMPLE t1 ref heap_idx heap_idx 82 const 9 Using where
329
Table Unique Key_name Seq_in_index Column_name
331
t1 FALSE heap_idx 1 name
332
t1 FALSE btree_idx 1 name
334
Table Unique Key_name Seq_in_index Column_name
336
t1 FALSE heap_idx 1 name
337
t1 FALSE btree_idx 1 name
338
create temporary table t3
340
a varchar(20) not null,
341
b varchar(20) not null,
344
insert into t3 select name, name from t1;
346
Table Unique Key_name Seq_in_index Column_name
350
Table Unique Key_name Seq_in_index Column_name
353
explain select * from t1 ignore key(btree_idx), t3 where t1.name='matt' and t3.a = concat('',t1.name) and t3.b=t1.name;
354
id select_type table type possible_keys key key_len ref rows Extra
355
1 SIMPLE t1 ref heap_idx heap_idx 82 const 9 Using where
356
1 SIMPLE t3 ref a a 164 func,const 7 Using where
357
drop table t1, t2, t3;
358
create temporary table t1 ( a int, index (a) ) engine=memory;
359
insert into t1 values (1),(2),(3),(4),(5);
360
select a from t1 where a in (1,3);
364
explain select a from t1 where a in (1,3);
365
id select_type table type possible_keys key key_len ref rows Extra
366
1 SIMPLE t1 range a a 5 NULL 4 Using where
369
CREATE TEMPORARY TABLE t1(col1 VARCHAR(32) COLLATE utf8_bin NOT NULL,
370
col2 VARCHAR(32) COLLATE utf8_bin NOT NULL,
371
UNIQUE KEY key1 USING HASH (col1, col2)) ENGINE=MEMORY;
372
INSERT INTO t1 VALUES('A', 'A');
373
INSERT INTO t1 VALUES('A ', 'A ');
374
ERROR 23000: Duplicate entry 'A -A ' for key 'key1'
376
CREATE TEMPORARY TABLE t1(col1 VARCHAR(32) COLLATE utf8_bin NOT NULL,
377
col2 VARCHAR(32) COLLATE utf8_bin NOT NULL,
378
UNIQUE KEY key1 USING HASH (col1, col2)) ENGINE=MEMORY;
379
INSERT INTO t1 VALUES('A', 'A');
380
INSERT INTO t1 VALUES('A ', 'A ');
381
ERROR 23000: Duplicate entry 'A -A ' for key 'key1'