14.3. The MERGE Storage Engine
The MERGE storage engine, also known as the
MRG_MyISAM engine, is a collection of identical
MyISAM tables that can be used as one.
“Identical” means that all tables have identical
column and index information. You cannot merge
MyISAM tables in which the columns are listed
in a different order, do not have exactly the same columns, or
have the indexes in different order. However, any or all of the
MyISAM tables can be compressed with
myisampack. See Section 8.4, “myisampack — Generate Compressed, Read-Only MyISAM Tables”.
Differences in table options such as
AVG_ROW_LENGTH, MAX_ROWS, or
PACK_KEYS do not matter.
When you create a MERGE table, MySQL creates
two files on disk. The files have names that begin with the table
name and have an extension to indicate the file type. An
.frm file stores the table format, and an
.MRG file contains the names of the tables
that should be used as one. The tables do not have to be in the
same database as the MERGE table itself.
You can use SELECT, DELETE,
UPDATE, and INSERT on
MERGE tables. You must have
SELECT, UPDATE, and
DELETE privileges on the
MyISAM tables that you map to a
MERGE table.
If you DROP the MERGE table,
you are dropping only the MERGE specification.
The underlying tables are not affected.
To create a MERGE table, you must specify a
UNION=(list-of-tables)
clause that indicates which MyISAM tables you
want to use as one. You can optionally specify an
INSERT_METHOD option if you want inserts for
the MERGE table to take place in the first or
last table of the UNION list. Use a value of
FIRST or LAST to cause
inserts to be made in the first or last table, respectively. If
you do not specify an INSERT_METHOD option or
if you specify it with a value of NO, attempts
to insert rows into the MERGE table result in
an error.
The following example shows how to create a
MERGE table:
mysql> CREATE TABLE t1 (
-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> message CHAR(20)) ENGINE=MyISAM;
mysql> CREATE TABLE t2 (
-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> message CHAR(20)) ENGINE=MyISAM;
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql> CREATE TABLE total (
-> a INT NOT NULL AUTO_INCREMENT,
-> message CHAR(20), INDEX(a))
-> ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
Note that the a column is indexed as a
PRIMARY KEY in the underlying
MyISAM tables, but not in the
MERGE table. There it is indexed but not as a
PRIMARY KEY because a MERGE
table cannot enforce uniqueness over the set of underlying tables.
After creating the MERGE table, you can issue
queries that operate on the group of tables as a whole:
mysql> SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table |
| 3 | t1 |
| 1 | Testing |
| 2 | table |
| 3 | t2 |
+---+---------+
Note that you can also manipulate the .MRG
file directly from outside of the MySQL server:
shell> cd /mysql-data-directory/current-database
shell> ls -1 t1 t2 > total.MRG
shell> mysqladmin flush-tables
To remap a MERGE table to a different
collection of MyISAM tables, you can use one of
the following methods:
DROP the MERGE table and
re-create it.
Use ALTER TABLE tbl_name
UNION=(...) to change the list of underlying tables.
Change the .MRG file and issue a
FLUSH TABLE statement for the
MERGE table and all underlying tables to
force the storage engine to read the new definition file.
MERGE tables can help you solve the following
problems:
Easily manage a set of log tables. For example, you can put
data from different months into separate tables, compress some
of them with myisampack, and then create a
MERGE table to use them as one.
Obtain more speed. You can split a big read-only table based
on some criteria, and then put individual tables on different
disks. A MERGE table on this could be much
faster than using the big table.
Perform more efficient searches. If you know exactly what you
are looking for, you can search in just one of the split
tables for some queries and use a MERGE
table for others. You can even have many different
MERGE tables that use overlapping sets of
tables.
Perform more efficient repairs. It is easier to repair
individual tables that are mapped to a
MERGE table than to repair a single large
table.
Instantly map many tables as one. A MERGE
table need not maintain an index of its own because it uses
the indexes of the individual tables. As a result,
MERGE table collections are
very fast to create or remap. (Note that
you must still specify the index definitions when you create a
MERGE table, even though no indexes are
created.)
If you have a set of tables from which you create a large
table on demand, you should instead create a
MERGE table on them on demand. This is much
faster and saves a lot of disk space.
Exceed the file size limit for the operating system. Each
MyISAM table is bound by this limit, but a
collection of MyISAM tables is not.
You can create an alias or synonym for a
MyISAM table by defining a
MERGE table that maps to that single table.
There should be no really notable performance impact from
doing this (only a couple of indirect calls and
memcpy() calls for each read).
The disadvantages of MERGE tables are:
You can use only identical MyISAM tables
for a MERGE table.
You cannot use a number of MyISAM features
in MERGE tables. For example, you cannot
create FULLTEXT indexes on
MERGE tables. (You can, of course, create
FULLTEXT indexes on the underlying
MyISAM tables, but you cannot search the
MERGE table with a full-text search.)
If the MERGE table is non-temporary, all
underlying MyISAM tables must be
non-temporary, too. If the MERGE table is
temporary, the MyISAM tables can be any mix
of temporary and non-temporary.
MERGE tables use more file descriptors. If
10 clients are using a MERGE table that
maps to 10 tables, the server uses (10 × 10) + 10 file
descriptors. (10 data file descriptors for each of the 10
clients, and 10 index file descriptors shared among the
clients.)
Key reads are slower. When you read a key, the
MERGE storage engine needs to issue a read
on all underlying tables to check which one most closely
matches the given key. To read the next key, the
MERGE storage engine needs to search the
read buffers to find the next key. Only when one key buffer is
used up does the storage engine need to read the next key
block. This makes MERGE keys much slower on
eq_ref searches, but not much slower on
ref searches. See
Section 7.2.1, “Optimizing Queries with EXPLAIN”, for more information about
eq_ref and ref.
Additional resources
