index_merge.result

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· RESULT 代码 · 共 519 行 · 第 1/2 页

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; Using index
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;
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(1);
CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b));
INSERT INTO t2(a,b) VALUES
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(1,2);
LOCK TABLES t1 WRITE, t2 WRITE;
INSERT INTO t2(a,b) VALUES(1,2);
SELECT t2.a FROM t1,t2 WHERE t2.b=2 AND t2.a=1;
a
1
1
UNLOCK TABLES;
DROP TABLE t1, t2;
CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`filler` char(200) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
insert into t1 values
(0, 'filler', 0), (1, 'filler', 1), (2, 'filler', 2), (3, 'filler', 3), 
(4, 'filler', 4), (5, 'filler', 5), (6, 'filler', 6), (7, 'filler', 7), 
(8, 'filler', 8), (9, 'filler', 9), (0, 'filler', 0), (1, 'filler', 1), 
(2, 'filler', 2), (3, 'filler', 3), (4, 'filler', 4), (5, 'filler', 5), 
(6, 'filler', 6), (7, 'filler', 7), (8, 'filler', 8), (9, 'filler', 9), 
(10, 'filler', 10), (11, 'filler', 11), (12, 'filler', 12), (13, 'filler', 13),
(14, 'filler', 14), (15, 'filler', 15), (16, 'filler', 16), (17, 'filler', 17), 
(18, 'filler', 18), (19, 'filler', 19), (4, '5      ', 0), (5, '4      ', 0), 
(4, '4      ', 0), (4, 'qq     ', 5), (5, 'qq     ', 4), (4, 'zz     ', 4);
create table t2(
`a` int(11) DEFAULT NULL,
`filler` char(200) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
KEY USING BTREE (`a`),
KEY USING BTREE (`b`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
insert into t2 select * from t1;
must use sort-union rather than union:
explain select * from t1 where a=4 or b=4;
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 sort_union(a,b); Using where
select * from t1 where a=4 or b=4;
a	filler	b
4	4	0
4	5	0
4	filler	4
4	filler	4
4	qq	5
4	zz	4
5	qq	4
select * from t1 ignore index(a,b) where a=4 or b=4;
a	filler	b
4	4	0
4	5	0
4	filler	4
4	filler	4
4	qq	5
4	zz	4
5	qq	4
must use union, not sort-union:
explain select * from t2 where a=4 or b=4;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index_merge	a,b	a,b	5,5	NULL	#	Using union(a,b); Using where
select * from t2 where a=4 or b=4;
a	filler	b
4	4	0
4	5	0
4	filler	4
4	filler	4
4	qq	5
4	zz	4
5	qq	4
drop table t1, t2;