index_merge.result

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

drop table if exists t0, t1, t2, t3, t4;
create table t0
(
key1 int not null, 
INDEX i1(key1)
);
alter table t0 add key2 int not null, add index i2(key2);
alter table t0 add key3 int not null, add index i3(key3);
alter table t0 add key4 int not null, add index i4(key4);
alter table t0 add key5 int not null, add index i5(key5);
alter table t0 add key6 int not null, add index i6(key6);
alter table t0 add key7 int not null, add index i7(key7);
alter table t0 add key8 int not null, add index i8(key8);
update t0 set key2=key1,key3=key1,key4=key1,key5=key1,key6=key1,key7=key1,key8=1024-key1;
analyze table t0;
Table	Op	Msg_type	Msg_text
test.t0	analyze	status	OK
explain select * from t0 where key1 < 3 or key1 > 1020;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	range	i1	i1	4	NULL	78	Using where
explain 
select * from t0 where key1 < 3 or key2 > 1020;
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	45	Using sort_union(i1,i2); Using where
select * from t0 where key1 < 3 or key2 > 1020;
key1	key2	key3	key4	key5	key6	key7	key8
1	1	1	1	1	1	1	1023
2	2	2	2	2	2	2	1022
1021	1021	1021	1021	1021	1021	1021	3
1022	1022	1022	1022	1022	1022	1022	2
1023	1023	1023	1023	1023	1023	1023	1
1024	1024	1024	1024	1024	1024	1024	0
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
explain
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
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	11	Using sort_union(i1,i2); Using where
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
key1	key2	key3	key4	key5	key6	key7	key8
31	31	31	31	31	31	31	993
32	32	32	32	32	32	32	992
33	33	33	33	33	33	33	991
34	34	34	34	34	34	34	990
35	35	35	35	35	35	35	989
36	36	36	36	36	36	36	988
37	37	37	37	37	37	37	987
38	38	38	38	38	38	38	986
39	39	39	39	39	39	39	985
explain select * from t0 ignore index (i2) where key1 < 3 or key2 <4;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	ALL	i1	NULL	NULL	NULL	1024	Using where
explain select * from t0 where (key1 < 3 or key2 <4) and key3 = 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	ref	i1,i2,i3	i3	4	const	1	Using where
explain select * from t0 use index (i1,i2) where (key1 < 3 or key2 <4) and key3 = 50;
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
explain select * from t0 where (key1 > 1 or key2  > 2);
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
explain select * from t0 force index (i1,i2) where (key1 > 1 or key2  > 2);
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	1024	Using sort_union(i1,i2); Using where
explain 
select * from t0 where key1<3 or key2<3 or (key1>5 and key1<8) or 
(key1>10 and key1<12) or (key2>100 and key2<110);
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	17	Using sort_union(i1,i2); Using where
explain select * from t0 where key2 = 45 or key1 <=> null;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	range	i1,i2	i2	4	NULL	1	Using where
explain select * from t0 where key2 = 45 or key1 is not null;
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
explain select * from t0 where key2 = 45 or key1 is null;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	ref	i2	i2	4	const	1	
explain select * from t0 where key2=10 or key3=3 or key4 <=> null;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	index_merge	i2,i3,i4	i2,i3	4,4	NULL	2	Using union(i2,i3); Using where
explain select * from t0 where key2=10 or key3=3 or key4 is null;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	index_merge	i2,i3	i2,i3	4,4	NULL	2	Using union(i2,i3); Using where
explain select key1 from t0 where (key1 <=> null) or (key2 < 5) or 
(key3=10) or (key4 <=> null);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	index_merge	i1,i2,i3,i4	i2,i3	4,4	NULL	6	Using sort_union(i2,i3); Using where
explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or 
(key3=10) or (key4 <=> null);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	index_merge	i1,i3,i4	i1,i3	4,4	NULL	6	Using sort_union(i1,i3); Using where
explain select * from t0 where 
(key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 5 or key6 < 5);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	index_merge	i1,i2,i3,i4,i5,i6	i1,i2	4,4	NULL	6	Using sort_union(i1,i2); Using where
explain
select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	index_merge	i1,i2,i3	i1,i2	4,4	NULL	9	Using sort_union(i1,i2); Using where
select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 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
4	4	4	4	4	4	4	1020
5	5	5	5	5	5	5	1019
explain select * from t0 where 
(key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 2 or key6 < 2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	index_merge	i1,i2,i3,i4,i5,i6	i1,i2	4,4	NULL	6	Using sort_union(i1,i2); Using where
explain select * from t0 where 
(key1 < 3 or key2 < 3) and (key3 < 100);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	range	i1,i2,i3	i3	4	NULL	95	Using where
explain select * from t0 where
(key1 < 3 or key2 < 3) and (key3 < 1000);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	ALL	i1,i2,i3	NULL	NULL	NULL	1024	Using where
explain select * from t0 where 
((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4)) 
or 
key2 > 5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	ALL	i1,i2,i3	NULL	NULL	NULL	1024	Using where
explain select * from t0 where
((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
or
key1 < 7;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	index_merge	i1,i2,i3	i1,i2	4,4	NULL	10	Using sort_union(i1,i2); Using where
select * from t0 where
((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
or
key1 < 7;
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
4	4	4	4	4	4	4	1020
5	5	5	5	5	5	5	1019
6	6	6	6	6	6	6	1018
explain select * from t0 where 
((key1 < 4 or key2 < 4) and (key3 <5 or key5 < 4)) 
or 
((key5 < 5 or key6 < 6) and (key7 <7 or key8 < 4));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	index_merge	i1,i2,i3,i5,i6,i7,i8	i1,i2,i5,i6	4,4,4,4	NULL	19	Using sort_union(i1,i2,i5,i6); Using where
explain select * from t0 where
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key7 <7 or key8 < 4) and (key5 < 5 or key6 < 6));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	index_merge	i1,i2,i3,i5,i6,i7,i8	i3,i5,i7,i8	4,4,4,4	NULL	20	Using sort_union(i3,i5,i7,i8); Using where
explain select * from t0 where
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 <7 or key5 < 2) and (key5 < 5 or key6 < 6));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	index_merge	i1,i2,i3,i5,i6	i3,i5	4,4	NULL	11	Using sort_union(i3,i5); Using where
explain select * from t0 where
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
(((key3 <7 and key7 < 6) or key5 < 2) and (key5 < 5 or key6 < 6));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	index_merge	i1,i2,i3,i5,i6,i7	i3,i5	4,4	NULL	11	Using sort_union(i3,i5); Using where
explain select * from t0 where
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	ALL	i1,i2,i3,i5,i6	NULL	NULL	NULL	1024	Using where
explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	index_merge	i1,i2,i3,i5,i6	i3,i5	0,4	NULL	1024	Using sort_union(i3,i5); Using where
select * from t0 where key1 < 5 or key8 < 4 order by key1;
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
4	4	4	4	4	4	4	1020
1021	1021	1021	1021	1021	1021	1021	3
1022	1022	1022	1022	1022	1022	1022	2
1023	1023	1023	1023	1023	1023	1023	1
1024	1024	1024	1024	1024	1024	1024	0
explain
select * from t0 where key1 < 5 or key8 < 4 order by key1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	index_merge	i1,i8	i1,i8	4,4	NULL	9	Using sort_union(i1,i8); Using where; Using filesort
create table t2 like t0;
insert into t2 select * from t0;
alter table t2 add index i1_3(key1, key3);
alter table t2 add index i2_3(key2, key3);
alter table t2 drop index i1;
alter table t2 drop index i2;
alter table t2 add index i321(key3, key2, key1);
explain select key3 from t2 where key1 = 100 or key2 = 100;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index_merge	i1_3,i2_3	i1_3,i2_3	4,4	NULL	2	Using sort_union(i1_3,i2_3); Using where
explain select key3 from t2 where key1 <100 or key2 < 100;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	i1_3,i2_3	i321	12	NULL	1024	Using where; Using index
explain select key7 from t2 where key1 <100 or key2 < 100;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	i1_3,i2_3	NULL	NULL	NULL	1024	Using where
create table t4 (
key1a int not null,
key1b int not null,
key2  int not null,
key2_1 int not null,