drop table if exists t1,t2,t3,t4,t5,t6; create table t1 (a int not null primary key auto_increment, message char(20)); create table t2 (a int not null primary key auto_increment, message char(20)); INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1"); INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2"); create table t3 (a int not null, b char(20), key(a)) type=MERGE UNION=(t1,t2); select * from t3; a b 1 Testing 2 table 3 t1 1 Testing 2 table 3 t2 select * from t3 order by a desc; a b 3 t1 3 t2 2 table 2 table 1 Testing 1 Testing drop table t3; insert into t1 select NULL,message from t2; insert into t2 select NULL,message from t1; insert into t1 select NULL,message from t2; insert into t2 select NULL,message from t1; insert into t1 select NULL,message from t2; insert into t2 select NULL,message from t1; insert into t1 select NULL,message from t2; insert into t2 select NULL,message from t1; insert into t1 select NULL,message from t2; insert into t2 select NULL,message from t1; insert into t1 select NULL,message from t2; create table t3 (a int not null, b char(20), key(a)) type=MERGE UNION=(test.t1,test.t2); explain select * from t3 where a < 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 range a a 4 NULL 18 Using where explain select * from t3 where a > 10 and a < 20; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 range a a 4 NULL 16 Using where select * from t3 where a = 10; a b 10 Testing 10 Testing select * from t3 where a < 10; a b 1 Testing 1 Testing 2 table 2 table 3 t1 3 t2 4 Testing 4 Testing 5 table 5 table 6 t1 6 t2 7 Testing 7 Testing 8 table 8 table 9 t2 9 t2 select * from t3 where a > 10 and a < 20; a b 11 table 11 table 12 t1 12 t1 13 Testing 13 Testing 14 table 14 table 15 t2 15 t2 16 Testing 16 Testing 17 table 17 table 18 t2 18 t2 19 Testing 19 Testing explain select a from t3 order by a desc limit 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 index NULL a 4 NULL 1131 Using index select a from t3 order by a desc limit 10; a 699 698 697 696 695 694 693 692 691 690 select a from t3 order by a desc limit 300,10; a 416 415 415 414 414 413 413 412 412 411 delete from t3 where a=3; select * from t3 where a < 10; a b 1 Testing 1 Testing 2 table 2 table 4 Testing 4 Testing 5 table 5 table 6 t2 6 t1 7 Testing 7 Testing 8 table 8 table 9 t2 9 t2 delete from t3 where a >= 6 and a <= 8; select * from t3 where a < 10; a b 1 Testing 1 Testing 2 table 2 table 4 Testing 4 Testing 5 table 5 table 9 t2 9 t2 update t3 set a=3 where a=9; select * from t3 where a < 10; a b 1 Testing 1 Testing 2 table 2 table 3 t2 3 t2 4 Testing 4 Testing 5 table 5 table update t3 set a=6 where a=7; select * from t3 where a < 10; a b 1 Testing 1 Testing 2 table 2 table 3 t2 3 t2 4 Testing 4 Testing 5 table 5 table show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `a` int(11) NOT NULL default '0', `b` char(20) character set latin1 default NULL, KEY `a` (`a`) ) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2) create table t4 (a int not null, b char(10), key(a)) type=MERGE UNION=(t1,t2); select * from t4; Can't open file: 't4.MRG'. (errno: 143) create table t5 (a int not null, b char(10), key(a)) type=MERGE UNION=(test.t1,test_2.t2); Incorrect table definition; All MERGE tables must be in the same database drop table if exists t5,t4,t3,t1,t2; create table t1 (c char(10)) type=myisam; create table t2 (c char(10)) type=myisam; create table t3 (c char(10)) union=(t1,t2) type=merge; insert into t1 (c) values ('test1'); insert into t1 (c) values ('test1'); insert into t1 (c) values ('test1'); insert into t2 (c) values ('test2'); insert into t2 (c) values ('test2'); insert into t2 (c) values ('test2'); select * from t3; c test1 test1 test1 test2 test2 test2 select * from t3; c test1 test1 test1 test2 test2 test2 delete from t3 where 1=1; select * from t3; c select * from t1; c drop table t3,t2,t1; CREATE TABLE t1 (incr int not null, othr int not null, primary key(incr)); CREATE TABLE t2 (incr int not null, othr int not null, primary key(incr)); CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr)) TYPE=MERGE UNION=(t1,t2); SELECT * from t3; incr othr INSERT INTO t1 VALUES ( 1,10),( 3,53),( 5,21),( 7,12),( 9,17); INSERT INTO t2 VALUES ( 2,24),( 4,33),( 6,41),( 8,26),( 0,32); INSERT INTO t1 VALUES (11,20),(13,43),(15,11),(17,22),(19,37); INSERT INTO t2 VALUES (12,25),(14,31),(16,42),(18,27),(10,30); SELECT * from t3 where incr in (1,2,3,4) order by othr; incr othr 1 10 2 24 4 33 3 53 alter table t3 UNION=(t1); select count(*) from t3; count(*) 10 alter table t3 UNION=(t1,t2); select count(*) from t3; count(*) 20 alter table t3 TYPE=MYISAM; select count(*) from t3; count(*) 20 drop table t3; CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr)) TYPE=MERGE UNION=(t1,t2); show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `incr` int(11) NOT NULL default '0', `othr` int(11) NOT NULL default '0', PRIMARY KEY (`incr`) ) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2) alter table t3 drop primary key; show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `incr` int(11) NOT NULL default '0', `othr` int(11) NOT NULL default '0' ) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2) drop table t3,t2,t1; create table t1 (a int not null) type=merge; select * from t1; a drop table t1; drop table if exists t3, t2, t1; create table t1 (a int not null, b int not null, key(a,b)); create table t2 (a int not null, b int not null, key(a,b)); create table t3 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2); insert into t1 values (1,2),(2,1),(0,0),(4,4),(5,5),(6,6); insert into t2 values (1,1),(2,2),(0,0),(4,4),(5,5),(6,6); flush tables; select * from t3 where a=1 order by b limit 2; a b 1 1 1 2 drop table t3,t1,t2; drop table if exists t6, t5, t4, t3, t2, t1; create table t1 (a int not null, b int not null auto_increment, primary key(a,b)); create table t2 (a int not null, b int not null auto_increment, primary key(a,b)); create table t3 (a int not null, b int not null, key(a,b)) UNION=(t1,t2) INSERT_METHOD=NO; create table t4 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=NO; create table t5 (a int not null, b int not null auto_increment, primary key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST; create table t6 (a int not null, b int not null auto_increment, primary key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', KEY `a` (`a`,`b`) ) TYPE=MyISAM CHARSET=latin1 show create table t4; Table Create Table t4 CREATE TABLE `t4` ( `a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', KEY `a` (`a`,`b`) ) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2) show create table t5; Table Create Table t5 CREATE TABLE `t5` ( `a` int(11) NOT NULL default '0', `b` int(11) NOT NULL auto_increment, PRIMARY KEY (`a`,`b`) ) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=FIRST UNION=(t1,t2) show create table t6; Table Create Table t6 CREATE TABLE `t6` ( `a` int(11) NOT NULL default '0', `b` int(11) NOT NULL auto_increment, PRIMARY KEY (`a`,`b`) ) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=LAST UNION=(t1,t2) insert into t1 values (1,NULL),(1,NULL),(1,NULL),(1,NULL); insert into t2 values (2,NULL),(2,NULL),(2,NULL),(2,NULL); select * from t3 order by b,a limit 3; a b select * from t4 order by b,a limit 3; a b 1 1 2 1 1 2 select * from t5 order by b,a limit 3,3; a b 2 2 1 3 2 3 select * from t6 order by b,a limit 6,3; a b 1 4 2 4 insert into t5 values (5,1),(5,2); insert into t6 values (6,1),(6,2); select * from t1 order by a,b; a b 1 1 1 2 1 3 1 4 5 1 5 2 select * from t2 order by a,b; a b 2 1 2 2 2 3 2 4 6 1 6 2 select * from t4 order by a,b; a b 1 1 1 2 1 3 1 4 2 1 2 2 2 3 2 4 5 1 5 2 6 1 6 2 insert into t3 values (3,1),(3,2),(3,3),(3,4); select * from t3 order by a,b; a b 3 1 3 2 3 3 3 4 alter table t4 UNION=(t1,t2,t3); show create table t4; Table Create Table t4 CREATE TABLE `t4` ( `a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', KEY `a` (`a`,`b`) ) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2,t3) select * from t4 order by a,b; a b 1 1 1 2 1 3 1 4 2 1 2 2 2 3 2 4 3 1 3 2 3 3 3 4 5 1 5 2 6 1 6 2 alter table t4 INSERT_METHOD=FIRST; show create table t4; Table Create Table t4 CREATE TABLE `t4` ( `a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', KEY `a` (`a`,`b`) ) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=FIRST UNION=(t1,t2,t3) insert into t4 values (4,1),(4,2); select * from t1 order by a,b; a b 1 1 1 2 1 3 1 4 4 1 4 2 5 1 5 2 select * from t2 order by a,b; a b 2 1 2 2 2 3 2 4 6 1 6 2 select * from t3 order by a,b; a b 3 1 3 2 3 3 3 4 select * from t4 order by a,b; a b 1 1 1 2 1 3 1 4 2 1 2 2 2 3 2 4 3 1 3 2 3 3 3 4 4 1 4 2 5 1 5 2 6 1 6 2 select * from t5 order by a,b; a b 1 1 1 2 1 3 1 4 2 1 2 2 2 3 2 4 4 1 4 2 5 1 5 2 6 1 6 2 select 1; 1 1 insert into t5 values (1,NULL),(5,NULL); insert into t6 values (2,NULL),(6,NULL); select * from t1 order by a,b; a b 1 1 1 2 1 3 1 4 1 5 4 1 4 2 5 1 5 2 5 3 select * from t2 order by a,b; a b 2 1 2 2 2 3 2 4 2 5 6 1 6 2 6 3 select * from t5 order by a,b; a b 1 1 1 2 1 3 1 4 1 5 2 1 2 2 2 3 2 4 2 5 4 1 4 2 5 1 5 2 5 3 6 1 6 2 6 3 select * from t6 order by a,b; a b 1 1 1 2 1 3 1 4 1 5 2 1 2 2 2 3 2 4 2 5 4 1 4 2 5 1 5 2 5 3 6 1 6 2 6 3 drop table if exists t6, t5, t4, t3, t2, t1; CREATE TABLE t1 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) TYPE=MyISAM; INSERT INTO t1 VALUES (1,1), (2,1); CREATE TABLE t2 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) TYPE=MyISAM; INSERT INTO t2 VALUES (1,2), (2,2); CREATE TABLE t ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', KEY a (a,b)) TYPE=MRG_MyISAM UNION=(t1,t2); select max(b) from t where a = 2; max(b) NULL select max(b) from t1 where a = 2; max(b) 1 drop table if exists t,t1,t2; drop table if exists t1, t2, t3, t4, t5, t6; create table t1 (a int not null); create table t2 (a int not null); insert into t1 values (1); insert into t2 values (2); create temporary table t3 (a int not null) TYPE=MERGE UNION=(t1,t2); select * from t3; a 1 2 create temporary table t4 (a int not null); create temporary table t5 (a int not null); insert into t4 values (1); insert into t5 values (2); create temporary table t6 (a int not null) TYPE=MERGE UNION=(t4,t5); select * from t6; a 1 2 drop table if exists t1, t2, t3, t4, t5, t6; DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 ( fileset_id tinyint(3) unsigned NOT NULL default '0', file_code varchar(32) NOT NULL default '', fileset_root_id tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (fileset_id,file_code), KEY files (fileset_id,fileset_root_id) ) TYPE=MyISAM; INSERT INTO t1 VALUES (2, '0000000111', 1), (2, '0000000112', 1), (2, '0000000113', 1), (2, '0000000114', 1), (2, '0000000115', 1), (2, '0000000116', 1), (2, '0000000117', 1), (2, '0000000118', 1), (2, '0000000119', 1), (2, '0000000120', 1); CREATE TABLE t2 ( fileset_id tinyint(3) unsigned NOT NULL default '0', file_code varchar(32) NOT NULL default '', fileset_root_id tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (fileset_id,file_code), KEY files (fileset_id,fileset_root_id) ) TYPE=MRG_MyISAM UNION=(t1); EXPLAIN SELECT * FROM t2 IGNORE INDEX (files) WHERE fileset_id = 2 AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range PRIMARY PRIMARY 33 NULL 5 Using where EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2 AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range PRIMARY,files PRIMARY 33 NULL 5 Using where EXPLAIN SELECT * FROM t1 WHERE fileset_id = 2 AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY,files PRIMARY 33 NULL 5 Using where EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2 AND file_code = '0000000115' LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 const PRIMARY,files PRIMARY 33 const,const 1 DROP TABLE IF EXISTS t1, t2;