heap_btree.result 5.96 KB
Newer Older
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
drop table if exists t1;
create table t1 (a int not null,b int not null, primary key using BTREE (a)) type=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100;
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
t1	0	PRIMARY	1	a	A	NULL	NULL	NULL		BTREE	
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 BTREE (c,a);
drop table t1;
create table t1 (a int not null,b int not null, primary key using BTREE (a)) type=heap comment="testing heaps";
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;
create table t1 (a int not null,b int not null, primary key using BTREE (a)) type=heap comment="testing heaps";
insert into t1 values(1,1),(2,2),(3,3),(4,4);
alter table t1 modify a int not null auto_increment, type=myisam, comment="new myisam table";
select * from t1;
a	b
1	1
2	2
3	3
4	4
drop table t1;
create table t1 (a int not null) type=heap;
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 BTREE (a);
select * from t1 where a > 736494;
a
802616
869751
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
alter table t1 type=myisam;
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	where used; Using index
70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86
drop table t1;
create table t1 (x int not null, y int not null, key x  using BTREE (x), unique y  using BTREE (y))
type=heap;
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	1
1	3
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 89
1	SIMPLE	t1	ALL	x	NULL	NULL	NULL	6	
1	SIMPLE	t2	eq_ref	y	y	4	t1.x	1	
90 91 92 93 94 95 96
drop table t1;
create table t1 (a int) type=heap;
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 BTREE (a,b),  key  using BTREE (b)  ) TYPE=HEAP;
98 99 100 101 102 103 104 105 106 107 108 109 110 111 112
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	1
1	2
1	3
1	4
1	5
1	6
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	1
1	1
1	2
113
1	2
114
1	3
115 116
1	3
1	4
117 118
1	4
1	5
119 120
1	5
1	6
121
1	6
122
explain select * from tx where a=x order by a,b;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
123
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
124
x	SIMPLE	tx	ref	a	a	x	const	x	where used
125
explain select * from tx where a=x order by b;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
126
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
127
x	SIMPLE	tx	ref	a	a	x	const	x	where used
128 129 130 131 132
select * from t1 where b=1;
a	b
1	1
1	1
explain select * from tx where b=x;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
133
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
134
x	SIMPLE	tx	ref	b	b	x	const	x	where used
135 136 137 138 139
drop table t1;
create table t1 (id int unsigned not null, primary key  using BTREE (id)) type=HEAP;
insert into t1 values(1);
select max(id) from t1;
max(id)
140
1
141 142 143
insert into t1 values(2);
select max(id) from t1;
max(id)
144
2
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
replace into t1 values(1);
drop table t1;
create table t1 (n int) type=heap;
drop table t1;
create table t1 (n int) type=heap;
drop table if exists t1;
CREATE table t1(f1 int not null,f2 char(20) not 
null,index(f2)) type=heap;
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;
create table t1 (btn char(10) not null, key using BTREE (btn)) type=heap;
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
174
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
175
1	SIMPLE	t1	ALL	btn	NULL	NULL	NULL	14	where used
176 177 178 179 180
select * from t1 where btn like "q%";
btn
alter table t1 add column new_col char(1) not null, add key using BTREE (btn,new_col), drop key btn;
update t1 set new_col=btn;
explain select * from t1 where btn="a";
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
181
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
182
1	SIMPLE	t1	ref	btn	btn	10	const	1	where used
183
explain select * from t1 where btn="a" and new_col="a";
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	btn	btn	11	const,const	1	where used
186 187 188 189 190 191 192 193 194 195 196
drop table t1;
CREATE TABLE t1 (
a int default NULL,
b int default NULL,
KEY a using BTREE (a),
UNIQUE b using BTREE (b)
) type=heap;
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
197
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
198
1	SIMPLE	t1	ref	a	a	5	const	1	where used
199 200 201 202 203 204
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
205
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
206
1	SIMPLE	t1	ref	b	b	5	const	1	where used
207 208 209 210 211 212 213 214 215 216 217 218
SELECT * FROM t1 WHERE b<=>NULL;
a	b
99	NULL
INSERT INTO t1 VALUES (1,3);
Duplicate entry '3' for key 1
DROP TABLE t1;
CREATE TABLE t1 (a int not null, primary key using BTREE (a)) type=heap;
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;