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.5, “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.
Note: The use of
MERGE tables entails the following security
issue: If a user has access to MyISAM table
t, that user can create a
MERGE table m that
accesses t. However, if the user's
privileges on t are subsequently
revoked, the user can continue to access
t by doing so through
m. If this behavior is undesirable, you
can start the server with the new --skip-merge
option to disable the MERGE storage engine.
This option is available as of MySQL 5.0.24.
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;
The older term TYPE is supported as a synonym
for ENGINE for backward compatibility, but
ENGINE is the preferred term and
TYPE is deprecated.
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 |
+---+---------+
To remap a MERGE table to a different
collection of MyISAM tables, you can use one of
the following methods:
-
DROPtheMERGEtable and re-create it. -
Use
ALTER TABLEtbl_nameUNION=(...) to change the list of underlying tables.
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
MERGEtable 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
MERGEtable 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
MERGEtable for others. You can even have many differentMERGEtables that use overlapping sets of tables. -
Perform more efficient repairs. It is easier to repair individual tables that are mapped to a
MERGEtable than to repair a single large table. -
Instantly map many tables as one. A
MERGEtable need not maintain an index of its own because it uses the indexes of the individual tables. As a result,MERGEtable collections are very fast to create or remap. (Note that you must still specify the index definitions when you create aMERGEtable, 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
MERGEtable 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
MyISAMtable is bound by this limit, but a collection ofMyISAMtables is not. -
You can create an alias or synonym for a
MyISAMtable by defining aMERGEtable that maps to that single table. There should be no really notable performance impact from doing this (only a couple of indirect calls andmemcpy()calls for each read).
The disadvantages of MERGE tables are:
-
You can use only identical
MyISAMtables for aMERGEtable. -
You cannot use a number of
MyISAMfeatures inMERGEtables. For example, you cannot createFULLTEXTindexes onMERGEtables. (You can, of course, createFULLTEXTindexes on the underlyingMyISAMtables, but you cannot search theMERGEtable with a full-text search.) -
If the
MERGEtable is non-temporary, all underlyingMyISAMtables must be non-temporary, too. If theMERGEtable is temporary, theMyISAMtables can be any mix of temporary and non-temporary. -
MERGEtables use more file descriptors. If 10 clients are using aMERGEtable 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
MERGEstorage 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, theMERGEstorage 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 makesMERGEkeys much slower oneq_refsearches, but not much slower onrefsearches. See Section 7.2.1, “Optimizing Queries withEXPLAIN”, for more information abouteq_refandref.
Additional resources
-
A forum dedicated to the
MERGEstorage engine is available at http://forums.mysql.com/list.php?93.
The following are known problems with MERGE
tables:
-
If you use
ALTER TABLEto change aMERGEtable to another storage engine, the mapping to the underlying tables is lost. Instead, the rows from the underlyingMyISAMtables are copied into the altered table, which then uses the specified storage engine. -
REPLACEdoes not work. -
You cannot use
DROP TABLE,ALTER TABLE,DELETEwithout aWHEREclause,REPAIR TABLE,TRUNCATE TABLE,OPTIMIZE TABLE, orANALYZE TABLEon any of the tables that are mapped into an openMERGEtable. If you do so, theMERGEtable may still refer to the original table, which yields unexpected results. The easiest way to work around this deficiency is to ensure that noMERGEtables remain open by issuing aFLUSH TABLESstatement prior to performing any of those operations. -
DROP TABLEon a table that is in use by aMERGEtable does not work on Windows because theMERGEstorage engine's table mapping is hidden from the upper layer of MySQL. Windows does not allow open files to be deleted, so you first must flush allMERGEtables (withFLUSH TABLES) or drop theMERGEtable before dropping the table. -
A
MERGEtable cannot maintain uniqueness constraints over the entire table. When you perform anINSERT, the data goes into the first or lastMyISAMtable (depending on the value of theINSERT_METHODoption). MySQL ensures that unique key values remain unique within thatMyISAMtable, but not across all the tables in the collection. -
When you create or alter
MERGEtable, there is no check to ensure that the underlying tables are existingMyISAMtables and have identical structures. When theMERGEtable is used, MySQL checks that the row length for all mapped tables is equal, but this is not foolproof. If you create aMERGEtable from dissimilarMyISAMtables, you are very likely to run into strange problems.Similarly, if you create a
MERGEtable from non-MyISAMtables, or if you drop an underlying table or alter it to be a non-MyISAMtable, no error for theMERGEtable occurs until later when you attempt to use it. -
The order of indexes in the
MERGEtable and its underlying tables should be the same. If you useALTER TABLEto add aUNIQUEindex to a table used in aMERGEtable, and then useALTER TABLEto add a non-unique index on theMERGEtable, the index ordering is different for the tables if there was already a non-unique index in the underlying table. (This happens becauseALTER TABLEputsUNIQUEindexes before non-unique indexes to facilitate rapid detection of duplicate keys.) Consequently, queries on tables with such indexes may return unexpected results. -
If you encounter an error message similar to
ERROR 1017 (HY000): Can't find file: 'mm.MRG' (errno: 2) it generally indicates that some of the base tables are not using the MyISAM storage engine. Confirm that all tables are MyISAM. -
There is a limit of 232 (~4.295E+09)) rows to a
MERGEtable, just as there is with aMyISAM, it is therefore not possible to merge multipleMyISAMtables that exceed this limitation. However, you build MySQL with the--with-big-tablesoption then the row limitation is increased to (232)2 (1.844E+19) rows. See Section 2.9.2, “Typical configure Options”. Beginning with MySQL 5.0.4 all standard binaries are built with this option.