MySQL-Merge-Engine

From DevRandom

Using the world database (http://dev.mysql.com/doc/world-setup/en/world-setup.html) create two tables

CityA and CityB and insert records starting with A and B respectively.

CREATE TABLE `CityA` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ;

INSERT into `CityA` SELECT * from City where `Name` LIKE 'A%';
Query OK, 259 rows affected (0.10 sec)

CREATE TABLE `CityB` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) 

INSERT into `CityB` SELECT * from City where `Name` LIKE 'B%';
Query OK, 317 rows affected (0.00 sec)

Now create a merge table using the two.

CREATE TABLE `CityAB` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=MRG_MyISAM  UNION=(`CityA`,`CityB`)

Now test

mysql> select count(*) from CityA;
+----------+
| count(*) |
+----------+
|      259 | 
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from CityB;
+----------+
| count(*) |
+----------+
|      317 | 
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from CityAB;
+----------+
| count(*) |
+----------+
|      576 | 
+----------+
1 row in set (0.00 sec)

You can also review the file structure on the system

# ls -l /var/lib/mysql/world/City*
-rw-rw---- 1 mysql mysql  17353 Jun 14 17:42 /var/lib/mysql/world/CityA.MYD
-rw-rw---- 1 mysql mysql   5120 Jun 14 17:42 /var/lib/mysql/world/CityA.MYI
-rw-rw---- 1 mysql mysql   8710 Jun  9 03:38 /var/lib/mysql/world/CityA.frm

-rw-rw---- 1 mysql mysql     12 Jun  9 03:49 /var/lib/mysql/world/CityAB.MRG
-rw-rw---- 1 mysql mysql   8710 Jun  9 03:49 /var/lib/mysql/world/CityAB.frm

-rw-rw---- 1 mysql mysql  21239 Jun 14 17:43 /var/lib/mysql/world/CityB.MYD
-rw-rw---- 1 mysql mysql   6144 Jun 14 17:43 /var/lib/mysql/world/CityB.MYI
-rw-rw---- 1 mysql mysql   8710 Jun  9 03:38 /var/lib/mysql/world/CityB.frm

# cat /var/lib/mysql/world/CityAB.MRG 
CityA
CityB