index_merge_innodb.result

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

drop table if exists t1,t2;
create table t1
(
key1 int not null, 
key2 int not null, 
INDEX i1(key1),
INDEX i2(key2)
) engine=innodb;
explain select * from t1 where key1 < 5 or key2 > 197;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	i1,i2	i1,i2	4,4	NULL	8	Using sort_union(i1,i2); Using where
select * from t1 where key1 < 5 or key2 > 197;
key1	key2
0	200
1	199
2	198
3	197
4	196
explain select * from t1 where key1 < 3 or key2 > 195;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	i1,i2	i1,i2	4,4	NULL	8	Using sort_union(i1,i2); Using where
select * from t1 where key1 < 3 or key2 > 195;
key1	key2
0	200
1	199
2	198
3	197
4	196
alter table t1 add str1 char (255) not null, 
add zeroval int not null default 0,
add str2 char (255) not null,
add str3 char (255) not null;
update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if(key1 mod 2 = 0, 'a', 'A'));
alter table t1 add primary key (str1, zeroval, str2, str3);
explain select * from t1 where key1 < 5 or key2 > 197;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	i1,i2	i1,i2	4,4	NULL	8	Using sort_union(i1,i2); Using where
select * from t1 where key1 < 5 or key2 > 197;
key1	key2	str1	zeroval	str2	str3
4	196	aaa	0	bbb	196-2_a
3	197	aaa	0	bbb	197-1_A
2	198	aaa	0	bbb	198-1_a
1	199	aaa	0	bbb	199-0_A
0	200	aaa	0	bbb	200-0_a
explain select * from t1 where key1 < 3 or key2 > 195;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	i1,i2	i1,i2	4,4	NULL	8	Using sort_union(i1,i2); Using where
select * from t1 where key1 < 3 or key2 > 195;
key1	key2	str1	zeroval	str2	str3
4	196	aaa	0	bbb	196-2_a
3	197	aaa	0	bbb	197-1_A
2	198	aaa	0	bbb	198-1_a
1	199	aaa	0	bbb	199-0_A
0	200	aaa	0	bbb	200-0_a
drop table t1;
create table t1 (
pk    integer not null auto_increment primary key,
key1  integer,
key2  integer not null,
filler char  (200),
index (key1),
index (key2)
) engine=innodb;
show warnings;
Level	Code	Message
explain select pk from t1 where key1 = 1 and key2 = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	key1,key2	key1,key2	5,4	NULL	1	Using intersect(key1,key2); Using where; Using index
select pk from t1 where key2 = 1 and key1 = 1;
pk
26
27
select pk from t1 ignore index(key1,key2) where key2 = 1 and key1 = 1;
pk
26
27
drop table t1;
create table t1 (
pk int primary key auto_increment,
key1a  int,
key2a  int,
key1b  int,
key2b  int,
dummy1 int,
dummy2 int,
dummy3 int,
dummy4 int,
key3a  int,
key3b  int,
filler1 char (200),
index i1(key1a, key1b),
index i2(key2a, key2b),
index i3(key3a, key3b)
) engine=innodb;
create table t2 (a int);
insert into t2 values (0),(1),(2),(3),(4),(NULL);
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b) 
select A.a, B.a, C.a, D.a, C.a, D.a from t2 A,t2 B,t2 C, t2 D;
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b) 
select key1a, key1b, key2a, key2b, key3a, key3b from t1;
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b) 
select key1a, key1b, key2a, key2b, key3a, key3b from t1;
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
select count(*) from t1;
count(*)
5184
explain select count(*) from t1 where 
key1a = 2 and key1b is null and  key2a = 2 and key2b is null;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	i1,i2	i1,i2	10,10	NULL	3	Using intersect(i1,i2); Using where; Using index
select count(*) from t1 where 
key1a = 2 and key1b is null and key2a = 2 and key2b is null;
count(*)
4
explain select count(*) from t1 where 
key1a = 2 and key1b is null and key3a = 2 and key3b is null;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	i1,i3	i1,i3	10,10	NULL	3	Using intersect(i1,i3); Using where; Using index
select count(*) from t1 where 
key1a = 2 and key1b is null and key3a = 2 and key3b is null;
count(*)
4
drop table t1,t2;
create table t1 (
id1 int,
id2 date ,
index idx2 (id1,id2),
index idx1 (id2)  
) engine = innodb;
insert into t1 values(1,'20040101'), (2,'20040102');
select * from t1  where id1 = 1  and id2= '20040101';
id1	id2
1	2004-01-01
drop table t1;
drop view if exists v1;
CREATE TABLE t1 (
`oid` int(11) unsigned NOT NULL auto_increment,
`fk_bbk_niederlassung` int(11) unsigned NOT NULL,
`fk_wochentag` int(11) unsigned NOT NULL,
`uhrzeit_von` time NOT NULL COMMENT 'HH:MM',
`uhrzeit_bis` time NOT NULL COMMENT 'HH:MM',
`geloescht` tinyint(4) NOT NULL,
`version` int(5) NOT NULL,
PRIMARY KEY  (`oid`),
KEY `fk_bbk_niederlassung` (`fk_bbk_niederlassung`),
KEY `fk_wochentag` (`fk_wochentag`),
KEY `ix_version` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert  into t1 values 
(1, 38, 1, '08:00:00', '13:00:00', 0, 1),
(2, 38, 2, '08:00:00', '13:00:00', 0, 1),
(3, 38, 3, '08:00:00', '13:00:00', 0, 1),
(4, 38, 4, '08:00:00', '13:00:00', 0, 1),
(5, 38, 5, '08:00:00', '13:00:00', 0, 1),
(6, 38, 5, '08:00:00', '13:00:00', 1, 2),
(7, 38, 3, '08:00:00', '13:00:00', 1, 2),
(8, 38, 1, '08:00:00', '13:00:00', 1, 2),
(9, 38, 2, '08:00:00', '13:00:00', 1, 2),
(10, 38, 4, '08:00:00', '13:00:00', 1, 2),
(11, 38, 1, '08:00:00', '13:00:00', 0, 3),
(12, 38, 2, '08:00:00', '13:00:00', 0, 3),
(13, 38, 3, '08:00:00', '13:00:00', 0, 3),
(14, 38, 4, '08:00:00', '13:00:00', 0, 3),
(15, 38, 5, '08:00:00', '13:00:00', 0, 3),
(16, 38, 4, '08:00:00', '13:00:00', 0, 4),
(17, 38, 5, '08:00:00', '13:00:00', 0, 4),
(18, 38, 1, '08:00:00', '13:00:00', 0, 4),
(19, 38, 2, '08:00:00', '13:00:00', 0, 4),
(20, 38, 3, '08:00:00', '13:00:00', 0, 4),
(21, 7, 1, '08:00:00', '13:00:00', 0, 1),
(22, 7, 2, '08:00:00', '13:00:00', 0, 1),
(23, 7, 3, '08:00:00', '13:00:00', 0, 1),
(24, 7, 4, '08:00:00', '13:00:00', 0, 1),
(25, 7, 5, '08:00:00', '13:00:00', 0, 1);
create view v1 as 
select 
zeit1.oid AS oid, 
zeit1.fk_bbk_niederlassung AS fk_bbk_niederlassung,
zeit1.fk_wochentag AS fk_wochentag,
zeit1.uhrzeit_von AS uhrzeit_von, 
zeit1.uhrzeit_bis AS uhrzeit_bis, 
zeit1.geloescht AS geloescht,
zeit1.version AS version
from 
t1 zeit1
where 
(zeit1.version = 
(select max(zeit2.version) AS `max(version)`
   from t1 zeit2 
where 
((zeit1.fk_bbk_niederlassung = zeit2.fk_bbk_niederlassung) and 
(zeit1.fk_wochentag = zeit2.fk_wochentag) and 
(zeit1.uhrzeit_von = zeit2.uhrzeit_von) and 
(zeit1.uhrzeit_bis = zeit2.uhrzeit_bis)
)
)
)
and (zeit1.geloescht = 0);
select * from v1 where oid = 21;
oid	fk_bbk_niederlassung	fk_wochentag	uhrzeit_von	uhrzeit_bis	geloescht	version
21	7	1	08:00:00	13:00:00	0	1
drop view v1;
drop table t1;
CREATE TABLE t1(
t_cpac varchar(2) NOT NULL,
t_vers varchar(4) NOT NULL,
t_rele varchar(2) NOT NULL,
t_cust varchar(4) NOT NULL,
filler1 char(250) default NULL,
filler2 char(250) default NULL,
PRIMARY KEY (t_cpac,t_vers,t_rele,t_cust),
UNIQUE KEY IX_4 (t_cust,t_cpac,t_vers,t_rele),
KEY IX_5 (t_vers,t_rele,t_cust)
) ENGINE=InnoDB;
insert into t1 values
('tm','2.5 ','a ','    ','',''), ('tm','2.5U','a ','stnd','',''),
('da','3.3 ','b ','    ','',''), ('da','3.3U','b ','stnd','',''),
('tl','7.6 ','a ','    ','',''), ('tt','7.6 ','a ','    ','',''),
('bc','B61 ','a ','    ','',''), ('bp','B61 ','a ','    ','',''),
('ca','B61 ','a ','    ','',''), ('ci','B61 ','a ','    ','',''),
('cp','B61 ','a ','    ','',''), ('dm','B61 ','a ','    ','',''),
('ec','B61 ','a ','    ','',''), ('ed','B61 ','a ','    ','',''),
('fm','B61 ','a ','    ','',''), ('nt','B61 ','a ','    ','',''),
('qm','B61 ','a ','    ','',''), ('tc','B61 ','a ','    ','',''),
('td','B61 ','a ','    ','',''), ('tf','B61 ','a ','    ','',''),
('tg','B61 ','a ','    ','',''), ('ti','B61 ','a ','    ','',''),
('tp','B61 ','a ','    ','',''), ('ts','B61 ','a ','    ','',''),
('wh','B61 ','a ','    ','',''), ('bc','B61U','a ','stnd','',''),
('bp','B61U','a ','stnd','',''), ('ca','B61U','a ','stnd','',''),
('ci','B61U','a ','stnd','',''), ('cp','B61U','a ','stnd','',''),
('dm','B61U','a ','stnd','',''), ('ec','B61U','a ','stnd','',''),
('fm','B61U','a ','stnd','',''), ('nt','B61U','a ','stnd','',''),
('qm','B61U','a ','stnd','',''), ('tc','B61U','a ','stnd','',''),
('td','B61U','a ','stnd','',''), ('tf','B61U','a ','stnd','',''),
('tg','B61U','a ','stnd','',''), ('ti','B61U','a ','stnd','',''),
('tp','B61U','a ','stnd','',''), ('ts','B61U','a ','stnd','',''),
('wh','B61U','a ','stnd','','');
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t_cpac` varchar(2) NOT NULL,
  `t_vers` varchar(4) NOT NULL,
  `t_rele` varchar(2) NOT NULL,
  `t_cust` varchar(4) NOT NULL,
  `filler1` char(250) default NULL,
  `filler2` char(250) default NULL,
  PRIMARY KEY  (`t_cpac`,`t_vers`,`t_rele`,`t_cust`),
  UNIQUE KEY `IX_4` (`t_cust`,`t_cpac`,`t_vers`,`t_rele`),
  KEY `IX_5` (`t_vers`,`t_rele`,`t_cust`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6';
t_vers	t_rele	t_cust	filler1
7.6 	a 	    	
7.6 	a 	    	
select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6'
  and t_rele='a' and t_cust = ' ';
t_vers	t_rele	t_cust	filler1
7.6 	a 	    	
7.6 	a 	    	
drop table t1;