Advanced MySQL Features - High Performance MySQL - page 274

limited version of partitioning, they actually provide some features you can't get with
partitions. 3
The merge table is really just a container that holds the real tables. You specify which
tables to include with a special UNION syntax to CREATE TABLE . Here's an example that
demonstrates many aspects of merge tables:
mysql> CREATE TABLE t1(a INT NOT NULL PRIMARY KEY)ENGINE=MyISAM;
mysql> CREATE TABLE t2(a INT NOT NULL PRIMARY KEY)ENGINE=MyISAM;
mysql> INSERT INTO t1(a) VALUES(1),(2);
mysql> INSERT INTO t2(a) VALUES(1),(2);
mysql> CREATE TABLE mrg(a INT NOT NULL PRIMARY KEY)
-> ENGINE=MERGE UNION=(t1, t2) INSERT_METHOD=LAST;
mysql> SELECT a FROM mrg;
+------+
| a |
+------+
| 1 |
| 1 |
| 2 |
| 2 |
+------+
Notice that the underlying tables have exactly the same number and types of columns,
and that all indexes that exist on the merge table also exist on the underlying tables.
These are requirements when creating a merge table. Notice also that there's a primary
key on the sole column of each table, yet the resulting merge table has duplicate rows.
This is one of the limitations of merge tables: each table inside the merge behaves
normally, but the merge table doesn't enforce constraints over the entire set of tables.
The INSERT_METHOD=LAST instruction to the table tells MySQL to send all INSERT state-
ments to the last table in the merge. Specifying FIRST or LAST is the only control you
have over where rows inserted into the merge table are placed (you can still insert into
the underlying tables directly, though). Partitioned tables give more control over where
data is stored.
The results of an INSERT are visible in both the merge table and the underlying table:
mysql> INSERT INTO mrg(a) VALUES(3);
mysql> SELECT a FROM t2;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+
Merge tables have some other interesting features and limitations, such as what hap-
pens when you drop a merge table or one of its underlying tables. Dropping a merge
3. Some people call these features “foot-guns.”