heap_hash.result 11.4 KB
Newer Older
1
drop table if exists t1,t2;
2
create table t1 (a int not null,b int not null, primary key using HASH (a)) engine=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100;
3 4 5 6
insert into t1 values(1,1),(2,2),(3,3),(4,4);
delete from t1 where a=1 or a=0;
show keys from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
7
t1	0	PRIMARY	1	a	NULL	3	NULL	NULL		HASH	
8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
select * from t1;
a	b
2	2
3	3
4	4
select * from t1 where a=4;
a	b
4	4
update t1 set b=5 where a=4;
update t1 set b=b+1 where a>=3;
replace t1 values (3,3);
select * from t1;
a	b
2	2
3	3
4	6
alter table t1 add c int not null, add key using HASH (c,a);
drop table t1;
26
create table t1 (a int not null,b int not null, primary key using HASH (a)) engine=heap comment="testing heaps";
27 28 29 30 31
insert into t1 values(1,1),(2,2),(3,3),(4,4);
delete from t1 where a > 0;
select * from t1;
a	b
drop table t1;
32
create table t1 (a int not null,b int not null, primary key using HASH (a)) engine=heap comment="testing heaps";
33
insert into t1 values(1,1),(2,2),(3,3),(4,4);
34
alter table t1 modify a int not null auto_increment, engine=myisam, comment="new myisam table";
35 36 37 38 39 40 41
select * from t1;
a	b
1	1
2	2
3	3
4	4
drop table t1;
42
create table t1 (a int not null) engine=heap;
43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65
insert into t1 values (869751),(736494),(226312),(802616);
select * from t1 where a > 736494;
a
869751
802616
alter table t1 add unique uniq_id using HASH (a);
select * from t1 where a > 736494;
a
869751
802616
select * from t1 where a = 736494;
a
736494
select * from t1 where a=869751 or a=736494;
a
736494
869751
select * from t1 where a in (869751,736494,226312,802616);
a
226312
736494
802616
869751
66
alter table t1 engine=myisam;
67
explain select * from t1 where a in (869751,736494,226312,802616);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
68
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
69
1	SIMPLE	t1	range	uniq_id	uniq_id	4	NULL	4	Using where; Using index
70 71
drop table t1;
create table t1 (x int not null, y int not null, key x  using HASH (x), unique y  using HASH (y))
72
engine=heap;
73 74 75 76 77 78 79 80 81 82 83 84 85 86
insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6);
select * from t1 where x=1;
x	y
1	3
1	1
select * from t1,t1 as t2 where t1.x=t2.y;
x	y	x	y
1	1	1	1
2	2	2	2
1	3	1	1
2	4	2	2
2	5	2	2
2	6	2	2
explain select * from t1,t1 as t2 where t1.x=t2.y;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
87
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
88
1	SIMPLE	t1	ALL	x	NULL	NULL	NULL	6	
89
1	SIMPLE	t2	eq_ref	y	y	4	test.t1.x	1	
90
drop table t1;
91
create table t1 (a int) engine=heap;
92 93 94 95 96
insert into t1 values(1);
select max(a) from t1;
max(a)
1
drop table t1;
97
CREATE TABLE t1 ( a int not null default 0, b int not null default 0,  key  using HASH (a),  key  using HASH (b)  ) ENGINE=HEAP;
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
insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
select * from t1 where a=1;
a	b
1	6
1	5
1	4
1	3
1	2
1	1
insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
select * from t1 where a=1;
a	b
1	6
1	5
1	4
1	3
1	2
1	1
1	6
1	5
1	4
1	3
1	2
1	1
drop table t1;
123
create table t1 (id int unsigned not null, primary key  using HASH (id)) engine=HEAP;
124 125 126 127 128 129 130 131 132 133
insert into t1 values(1);
select max(id) from t1;
max(id)
1
insert into t1 values(2);
select max(id) from t1;
max(id)
2
replace into t1 values(1);
drop table t1;
134
create table t1 (n int) engine=heap;
135
drop table t1;
136
create table t1 (n int) engine=heap;
137 138
drop table if exists t1;
CREATE table t1(f1 int not null,f2 char(20) not 
139
null,index(f2)) engine=heap;
140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157
INSERT into t1 set f1=12,f2="bill";
INSERT into t1 set f1=13,f2="bill";
INSERT into t1 set f1=14,f2="bill";
INSERT into t1 set f1=15,f2="bill";
INSERT into t1 set f1=16,f2="ted";
INSERT into t1 set f1=12,f2="ted";
INSERT into t1 set f1=12,f2="ted";
INSERT into t1 set f1=12,f2="ted";
INSERT into t1 set f1=12,f2="ted";
delete from t1 where f2="bill";
select * from t1;
f1	f2
16	ted
12	ted
12	ted
12	ted
12	ted
drop table t1;
158
create table t1 (btn char(10) not null, key using HASH (btn)) engine=heap;
159 160
insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i");
explain select * from t1 where btn like "q%";
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
161
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
162
1	SIMPLE	t1	ALL	btn	NULL	NULL	NULL	14	Using where
163 164 165
select * from t1 where btn like "q%";
btn
alter table t1 add column new_col char(1) not null, add key using HASH (btn,new_col), drop key btn;
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
166
update t1 set new_col=left(btn,1);
167
explain select * from t1 where btn="a";
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
168
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
169
1	SIMPLE	t1	ALL	btn	NULL	NULL	NULL	11	Using where
170
explain select * from t1 where btn="a" and new_col="a";
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
171
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
172
1	SIMPLE	t1	ref	btn	btn	11	const,const	2	Using where
173 174 175 176 177 178
drop table t1;
CREATE TABLE t1 (
a int default NULL,
b int default NULL,
KEY a using HASH (a),
UNIQUE b using HASH (b)
179
) engine=heap;
180 181 182 183
INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3);
SELECT * FROM t1 WHERE a=NULL;
a	b
explain SELECT * FROM t1 WHERE a IS NULL;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
184
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
185
1	SIMPLE	t1	ref	a	a	5	const	1	Using where
186 187 188 189 190 191
SELECT * FROM t1 WHERE a<=>NULL;
a	b
NULL	99
SELECT * FROM t1 WHERE b=NULL;
a	b
explain SELECT * FROM t1 WHERE b IS NULL;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
192
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
193
1	SIMPLE	t1	ref	b	b	5	const	1	Using where
194 195 196 197
SELECT * FROM t1 WHERE b<=>NULL;
a	b
99	NULL
INSERT INTO t1 VALUES (1,3);
198
ERROR 23000: Duplicate entry '3' for key 1
199
DROP TABLE t1;
200
CREATE TABLE t1 (a int not null, primary key using HASH (a)) engine=heap;
201 202 203 204 205
INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
DELETE from t1 where a < 100;
SELECT * from t1;
a
DROP TABLE t1;
206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341
create table t1
(
a char(8) not null,
b char(20) not null,
c int not null,
key (a)
) engine=heap;
insert into t1 values ('aaaa', 'prefill-hash=5',0);
insert into t1 values ('aaab', 'prefill-hash=0',0);
insert into t1 values ('aaac', 'prefill-hash=7',0);
insert into t1 values ('aaad', 'prefill-hash=2',0);
insert into t1 values ('aaae', 'prefill-hash=1',0);
insert into t1 values ('aaaf', 'prefill-hash=4',0);
insert into t1 values ('aaag', 'prefill-hash=3',0);
insert into t1 values ('aaah', 'prefill-hash=6',0);
explain select * from t1 where a='aaaa';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	1	Using where
explain select * from t1 where a='aaab';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	1	Using where
explain select * from t1 where a='aaac';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	1	Using where
explain select * from t1 where a='aaad';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	1	Using where
insert into t1 select * from t1;
explain select * from t1 where a='aaaa';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
explain select * from t1 where a='aaab';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
explain select * from t1 where a='aaac';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
explain select * from t1 where a='aaad';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
create table t2 as select * from t1;
delete from t1;
insert into t1 select * from t2;
explain select * from t1 where a='aaaa';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
explain select * from t1 where a='aaab';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
explain select * from t1 where a='aaac';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
explain select * from t1 where a='aaad';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
drop table t1, t2;
create table t1 (
id int unsigned not null primary key auto_increment, 
name varchar(20) not null,
index heap_idx(name),
index btree_idx using btree(name)
) engine=heap;
create table t2 (
id int unsigned not null primary key auto_increment, 
name varchar(20) not null,
index btree_idx using btree(name),
index heap_idx(name)
) engine=heap;
insert into t1 (name) values ('Matt'), ('Lilu'), ('Corbin'), ('Carly'), 
('Suzy'), ('Hoppy'), ('Burrito'), ('Mimi'), ('Sherry'), ('Ben'), ('Phil'), 
('Emily'), ('Mike');
insert into t2 select * from t1;
explain select * from t1 where name='matt';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	heap_idx,btree_idx	heap_idx	20	const	1	Using where
explain select * from t2 where name='matt';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ref	btree_idx,heap_idx	btree_idx	20	const	1	Using where
explain select * from t1 where name='Lilu';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	heap_idx,btree_idx	heap_idx	20	const	1	Using where
explain select * from t2 where name='Lilu';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ref	btree_idx,heap_idx	btree_idx	20	const	1	Using where
explain select * from t1 where name='Phil';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	heap_idx,btree_idx	heap_idx	20	const	1	Using where
explain select * from t2 where name='Phil';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ref	btree_idx,heap_idx	btree_idx	20	const	1	Using where
explain select * from t1 where name='Lilu';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	heap_idx,btree_idx	heap_idx	20	const	1	Using where
explain select * from t2 where name='Lilu';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ref	btree_idx,heap_idx	btree_idx	20	const	1	Using where
insert into t1 (name) select name from t2;
insert into t1 (name) select name from t2;
insert into t1 (name) select name from t2;
insert into t1 (name) select name from t2;
insert into t1 (name) select name from t2;
insert into t1 (name) select name from t2;
select count(*) from t1 where name='Matt';
count(*)
7
explain select * from t1 ignore index (btree_idx) where name='matt';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	heap_idx	heap_idx	20	const	7	Using where
show index from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	PRIMARY	1	id	NULL	91	NULL	NULL		HASH	
t1	1	heap_idx	1	name	NULL	15	NULL	NULL		HASH	
t1	1	btree_idx	1	name	A	NULL	NULL	NULL		BTREE	
flush tables;
show index from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	PRIMARY	1	id	NULL	91	NULL	NULL		HASH	
t1	1	heap_idx	1	name	NULL	13	NULL	NULL		HASH	
t1	1	btree_idx	1	name	A	NULL	NULL	NULL		BTREE	
create table t3
(
a varchar(20) not null,
b varchar(20) not null,
key (a,b)
) engine=heap;
insert into t3 select name, name from t1;
show index from t3;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t3	1	a	1	a	NULL	NULL	NULL	NULL		HASH	
t3	1	a	2	b	NULL	15	NULL	NULL		HASH	
flush tables;
show index from t3;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t3	1	a	1	a	NULL	NULL	NULL	NULL		HASH	
t3	1	a	2	b	NULL	13	NULL	NULL		HASH	
drop table t1,t2;