index_merge.result

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

key2_2 int not null,
key3  int not null,
index i1a (key1a, key1b),
index i1b (key1b, key1a),
index i2_1(key2, key2_1),
index i2_2(key2, key2_1)
);
insert into t4 select key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 from t0;
select * from t4 where key1a = 3 or key1b = 4;
key1a	key1b	key2	key2_1	key2_2	key3
3	3	0	3	3	3
4	4	0	4	4	4
explain select * from t4 where key1a = 3 or key1b = 4;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t4	index_merge	i1a,i1b	i1a,i1b	4,4	NULL	2	Using sort_union(i1a,i1b); Using where
explain select * from t4 where key2 = 1 and (key2_1 = 1 or key3 = 5);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t4	ref	i2_1,i2_2	i2_1	4	const	10	Using where
explain select * from t4 where key2 = 1 and (key2_1 = 1 or key2_2 = 5);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t4	ref	i2_1,i2_2	i2_1	4	const	10	Using where
explain select * from t4 where key2_1 = 1 or key2_2 = 5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1024	Using where
create table t1 like t0;
insert into t1 select * from t0;
explain select * from t0 left join t1 on (t0.key1=t1.key1) 
where t0.key1=3 or t0.key2=4;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	index_merge	i1,i2	i1,i2	4,4	NULL	2	Using union(i1,i2); Using where
1	SIMPLE	t1	ref	i1	i1	4	test.t0.key1	1	
select * from t0 left join t1 on (t0.key1=t1.key1)
where t0.key1=3 or t0.key2=4;
key1	key2	key3	key4	key5	key6	key7	key8	key1	key2	key3	key4	key5	key6	key7	key8
3	3	3	3	3	3	3	1021	3	3	3	3	3	3	3	1021
4	4	4	4	4	4	4	1020	4	4	4	4	4	4	4	1020
explain 
select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	index_merge	i1,i2	i1,i2	4,4	NULL	2	Using union(i1,i2); Using where
1	SIMPLE	t1	ref	i1	i1	4	test.t0.key1	1	
explain 
select * from t0,t1 where (t0.key1=t1.key1) and 
(t0.key1=3 or t0.key2=4) and t1.key1<200;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	ALL	i1,i2	NULL	NULL	NULL	1024	Using where
1	SIMPLE	t1	ref	i1	i1	4	test.t0.key1	1	
explain 
select * from t0,t1 where (t0.key1=t1.key1) and 
(t0.key1=3 or t0.key2<4) and t1.key1=2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	ref	i1,i2	i1	4	const	1	Using where
1	SIMPLE	t1	ref	i1	i1	4	const	1	
explain select * from t0,t1 where t0.key1 = 5 and 
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	ref	i1	i1	4	const	1	
1	SIMPLE	t1	index_merge	i1,i8	i1,i8	4,4	NULL	2	Using union(i1,i8); Using where
explain select * from t0,t1 where t0.key1 < 3 and
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	range	i1	i1	4	NULL	3	Using where
1	SIMPLE	t1	ALL	i1,i8	NULL	NULL	NULL	1024	Range checked for each record (index map: 0x81)
explain select * from t1 where key1=3 or key2=4 
union select * from t1 where key1<4 or key3=5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	index_merge	i1,i2	i1,i2	4,4	NULL	2	Using union(i1,i2); Using where
2	UNION	t1	index_merge	i1,i3	i1,i3	4,4	NULL	5	Using sort_union(i1,i3); Using where
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
2	DERIVED	t1	index_merge	i1,i2	i1,i2	4,4	NULL	2	Using union(i1,i2); Using where
create table t3 like t0;
insert into t3 select * from t0;
alter table t3 add key9 int not null, add index i9(key9);
alter table t3 add keyA int not null, add index iA(keyA);
alter table t3 add keyB int not null, add index iB(keyB);
alter table t3 add keyC int not null, add index iC(keyC);
update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1;
explain select * from t3 where 
key1=1 or key2=2 or key3=3 or key4=4 or
key5=5 or key6=6 or key7=7 or key8=8 or
key9=9 or keyA=10 or keyB=11 or keyC=12;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	index_merge	i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC	i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC	4,4,4,4,4,4,4,4,4,4,4,4	NULL	12	Using union(i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC); Using where
select * from t3 where
key1=1 or key2=2 or key3=3 or key4=4 or
key5=5 or key6=6 or key7=7 or key8=8 or
key9=9 or keyA=10 or keyB=11 or keyC=12;
key1	key2	key3	key4	key5	key6	key7	key8	key9	keyA	keyB	keyC
1	1	1	1	1	1	1	1023	1	1	1	1
2	2	2	2	2	2	2	1022	2	2	2	2
3	3	3	3	3	3	3	1021	3	3	3	3
4	4	4	4	4	4	4	1020	4	4	4	4
5	5	5	5	5	5	5	1019	5	5	5	5
6	6	6	6	6	6	6	1018	6	6	6	6
7	7	7	7	7	7	7	1017	7	7	7	7
9	9	9	9	9	9	9	1015	9	9	9	9
10	10	10	10	10	10	10	1014	10	10	10	10
11	11	11	11	11	11	11	1013	11	11	11	11
12	12	12	12	12	12	12	1012	12	12	12	12
1016	1016	1016	1016	1016	1016	1016	8	1016	1016	1016	1016
explain select * from t0 where key1 < 3 or key2 < 4;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	index_merge	i1,i2	i1,i2	4,4	NULL	7	Using sort_union(i1,i2); Using where
select * from t0 where key1 < 3 or key2 < 4;
key1	key2	key3	key4	key5	key6	key7	key8
1	1	1	1	1	1	1	1023
2	2	2	2	2	2	2	1022
3	3	3	3	3	3	3	1021
update t0 set key8=123 where key1 < 3 or key2 < 4;
select * from t0 where key1 < 3 or key2 < 4;
key1	key2	key3	key4	key5	key6	key7	key8
1	1	1	1	1	1	1	123
2	2	2	2	2	2	2	123
3	3	3	3	3	3	3	123
delete from t0 where key1 < 3 or key2 < 4;
select * from t0 where key1 < 3 or key2 < 4;
key1	key2	key3	key4	key5	key6	key7	key8
select count(*) from t0;
count(*)
1021
drop table t4;
create table t4 (a int);
insert into t4 values (1),(4),(3);
set @save_join_buffer_size=@@join_buffer_size;
set join_buffer_size= 4000;
explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 
from t0 as A force index(i1,i2), t0 as B force index (i1,i2) 
where (A.key1 < 500000 or A.key2 < 3)
and   (B.key1 < 500000 or B.key2 < 3);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	A	index_merge	i1,i2	i1,i2	4,4	NULL	1013	Using sort_union(i1,i2); Using where
1	SIMPLE	B	index_merge	i1,i2	i1,i2	4,4	NULL	1013	Using sort_union(i1,i2); Using where
select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 
from t0 as A force index(i1,i2), t0 as B force index (i1,i2) 
where (A.key1 < 500000 or A.key2 < 3)
and   (B.key1 < 500000 or B.key2 < 3);
max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
10240
update t0 set key1=1;
explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 
from t0 as A force index(i1,i2), t0 as B force index (i1,i2) 
where (A.key1 = 1 or A.key2 = 1)
and   (B.key1 = 1 or B.key2 = 1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	A	index_merge	i1,i2	i1,i2	4,4	NULL	1020	Using union(i1,i2); Using where
1	SIMPLE	B	index_merge	i1,i2	i1,i2	4,4	NULL	1020	Using union(i1,i2); Using where
select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 
from t0 as A force index(i1,i2), t0 as B force index (i1,i2) 
where (A.key1 = 1 or A.key2 = 1)
and   (B.key1 = 1 or B.key2 = 1);
max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
8194
alter table t0 add filler1 char(200), add filler2 char(200), add filler3 char(200);
update t0 set key2=1, key3=1, key4=1, key5=1,key6=1,key7=1 where key7 < 500;
explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 
from t0 as A, t0 as B 
where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1)
and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7 = 1 or B.key8=1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	A	index_merge	i1,i2,i3,i4,i5,i6,i7?,i8	i2,i3,i4,i5,i6,i7?,i8	X	NULL	#	Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where
1	SIMPLE	B	index_merge	i1,i2,i3,i4,i5,i6,i7?,i8	i2,i3,i4,i5,i6,i7?,i8	X	NULL	#	Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where
select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 
from t0 as A, t0 as B 
where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1)
and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7 = 1 or B.key8=1);
max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
8186
set join_buffer_size= @save_join_buffer_size;
drop table t0, t1, t2, t3, t4;
CREATE TABLE t1 (
cola char(3) not null, colb char(3) not null,  filler char(200),
key(cola), key(colb)
);
INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
OPTIMIZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	optimize	status	OK
select count(*) from t1;
count(*)
8704
explain select * from t1 WHERE cola = 'foo' AND colb = 'bar';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	cola,colb	cola,colb	3,3	NULL	24	Using intersect(cola,colb); Using where
explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	cola,colb	cola,colb	3,3	NULL	24	Using intersect(cola,colb); Using where
drop table t1;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (
a int, b int, 
filler1 char(200), filler2 char(200),
key(a),key(b)
);
insert into t1 select @v:= A.a, @v, 't1', 'filler2' from t0 A, t0 B, t0 C;
create table t2 like t1;
create table t3 (
a int, b int, 
filler1 char(200), filler2 char(200),
key(a),key(b)
) engine=merge union=(t1,t2);
explain select * from t1 where a=1 and b=1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	a,b	a,b	5,5	NULL	#	Using intersect(a,b); Using where
explain select * from t3 where a=1 and b=1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	index_merge	a,b	a,b	5,5	NULL	#	Using intersect(a,b); Using where
drop table t3;
drop table t0, t1, t2;