drop table if exists t1,t2,t3,t4,t5,t6; drop database if exists mysqltest; 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)) engine=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)) engine=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 17 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, `b` char(20) DEFAULT NULL, KEY `a` (`a`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`) create table t4 (a int not null, b char(10), key(a)) engine=MERGE UNION=(t1,t2); select * from t4; ERROR HY000: All tables in the MERGE table are not identically defined alter table t4 add column c int; ERROR HY000: All tables in the MERGE table are not identically defined flush tables; select * from t4; ERROR HY000: All tables in the MERGE table are not identically defined create database mysqltest; create table mysqltest.t6 (a int not null primary key auto_increment, message char(20)); create table t5 (a int not null, b char(20), key(a)) engine=MERGE UNION=(test.t1,mysqltest.t6); show create table t5; Table Create Table t5 CREATE TABLE `t5` ( `a` int(11) NOT NULL, `b` char(20) DEFAULT NULL, KEY `a` (`a`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`mysqltest`.`t6`) alter table t5 engine=myisam; drop table t5, mysqltest.t6; drop database mysqltest; drop table t4,t3,t1,t2; create table t1 (c char(10)) engine=myisam; create table t2 (c char(10)) engine=myisam; create table t3 (c char(10)) union=(t1,t2) engine=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)) ENGINE=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 ENGINE=MYISAM; select count(*) from t3; count(*) 20 drop table t3; CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr)) ENGINE=MERGE UNION=(t1,t2); show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `incr` int(11) NOT NULL, `othr` int(11) NOT NULL, PRIMARY KEY (`incr`) ) ENGINE=MRG_MyISAM DEFAULT 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, `othr` int(11) NOT NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`) drop table t3,t2,t1; create table t1 (a int not null, key(a)) engine=merge; select * from t1; a drop table 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)) ENGINE=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; 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)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=NO; create table t5 (a int not null, b int not null auto_increment, primary key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST; create table t6 (a int not null, b int not null auto_increment, primary key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL, KEY `a` (`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show create table t4; Table Create Table t4 CREATE TABLE `t4` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL, KEY `a` (`a`,`b`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`) show create table t5; Table Create Table t5 CREATE TABLE `t5` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`a`,`b`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`,`t2`) show create table t6; Table Create Table t6 CREATE TABLE `t6` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`a`,`b`) ) ENGINE=MRG_MyISAM DEFAULT 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, `b` int(11) NOT NULL, KEY `a` (`a`,`b`) ) ENGINE=MRG_MyISAM DEFAULT 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, `b` int(11) NOT NULL, KEY `a` (`a`,`b`) ) ENGINE=MRG_MyISAM DEFAULT 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 insert into t1 values (99,NULL); select * from t4 where a+0 > 90; a b 99 1 insert t5 values (1,1); ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY' insert t6 values (2,1); ERROR 23000: Duplicate entry '2-1' for key 'PRIMARY' insert t5 values (1,1) on duplicate key update b=b+10; insert t6 values (2,1) on duplicate key update b=b+20; select * from t5 where a < 3; a b 1 2 1 3 1 4 1 5 1 11 2 2 2 3 2 4 2 5 2 21 drop table 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)) ENGINE=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)) ENGINE=MyISAM; INSERT INTO t2 VALUES (1,2), (2,2); CREATE TABLE t3 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', KEY a (a,b)) ENGINE=MRG_MyISAM UNION=(t1,t2); select max(b) from t3 where a = 2; max(b) 2 select max(b) from t1 where a = 2; max(b) 1 drop table t3,t1,t2; 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) ENGINE=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) ENGINE=MERGE UNION=(t4,t5); select * from t6; a 1 2 drop table t6, t3, t1, t2, t4, t5; 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) ) ENGINE=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) ) ENGINE=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 35 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 35 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 35 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 ref PRIMARY,files PRIMARY 35 const,const 1 Using where DROP TABLE t2, t1; create table t1 (x int, y int, index xy(x, y)); create table t2 (x int, y int, index xy(x, y)); create table t3 (x int, y int, index xy(x, y)) engine=merge union=(t1,t2); insert into t1 values(1, 2); insert into t2 values(1, 3); select * from t3 where x = 1 and y < 5 order by y; x y 1 2 1 3 select * from t3 where x = 1 and y < 5 order by y desc; x y 1 3 1 2 drop table t1,t2,t3; create table t1 (a int); create table t2 (a int); insert into t1 values (0); insert into t2 values (1); create table t3 engine=merge union=(t1, t2) select * from t1; ERROR HY000: You can't specify target table 't1' for update in FROM clause create table t3 engine=merge union=(t1, t2) select * from t2; ERROR HY000: You can't specify target table 't2' for update in FROM clause create table t3 engine=merge union=(t1, t2) select (select max(a) from t2); ERROR HY000: You can't specify target table 't2' for update in FROM clause drop table t1, t2; create table t1 ( a double(14,4), b varchar(10), index (a,b) ) engine=merge union=(t2,t3); create table t2 ( a double(14,4), b varchar(10), index (a,b) ) engine=myisam; create table t3 ( a double(14,4), b varchar(10), index (a,b) ) engine=myisam; insert into t2 values ( null, ''); insert into t2 values ( 9999999999.999, ''); insert into t3 select * from t2; select min(a), max(a) from t1; min(a) max(a) 9999999999.9990 9999999999.9990 flush tables; select min(a), max(a) from t1; min(a) max(a) 9999999999.9990 9999999999.9990 drop table t1, t2, t3; create table t1 (a int,b int,c int, index (a,b,c)); create table t2 (a int,b int,c int, index (a,b,c)); create table t3 (a int,b int,c int, index (a,b,c)) engine=merge union=(t1 ,t2); insert into t1 (a,b,c) values (1,1,0),(1,2,0); insert into t2 (a,b,c) values (1,1,1),(1,2,1); explain select a,b,c from t3 force index (a) where a=1 order by a,b,c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref a a 5 const 2 Using where; Using index select a,b,c from t3 force index (a) where a=1 order by a,b,c; a b c 1 1 0 1 1 1 1 2 0 1 2 1 explain select a,b,c from t3 force index (a) where a=1 order by a desc, b desc, c desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref a a 5 const 2 Using where; Using index select a,b,c from t3 force index (a) where a=1 order by a desc, b desc, c desc; a b c 1 2 1 1 2 0 1 1 1 1 1 0 show index from t3; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t3 1 a 1 a A NULL NULL NULL YES BTREE t3 1 a 2 b A NULL NULL NULL YES BTREE t3 1 a 3 c A NULL NULL NULL YES BTREE drop table t1, t2, t3; CREATE TABLE t1 ( a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10), UNIQUE (b) ) ENGINE=MyISAM; CREATE TABLE t2 ( a INT AUTO_INCREMENT, b VARCHAR(10), INDEX (a), INDEX (b) ) ENGINE=MERGE UNION (t1) INSERT_METHOD=FIRST; INSERT INTO t2 (b) VALUES (1) ON DUPLICATE KEY UPDATE b=2; INSERT INTO t2 (b) VALUES (1) ON DUPLICATE KEY UPDATE b=3; SELECT b FROM t2; b 3 DROP TABLE t1, t2; create table t1(a int); create table t2(a int); insert into t1 values (1); insert into t2 values (2); create table t3 (a int) engine=merge union=(t1, t2) insert_method=first; select * from t3; a 1 2 insert t2 select * from t2; select * from t2; a 2 2 insert t3 select * from t1; select * from t3; a 1 1 2 2 insert t1 select * from t3; select * from t1; a 1 1 1 1 2 2 select * from t2; a 2 2 select * from t3; a 1 1 1 1 2 2 2 2 check table t1, t2; Table Op Msg_type Msg_text test.t1 check status OK test.t2 check status OK drop table t1, t2, t3;