MySQL 5.0 Reference Manual  /  Storage Engines  /  The MERGE Storage Engine

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 4.6.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 underlying MyISAM tables that should be used as one. The tables do not have to be in the same database as the MERGE table.

You can use SELECT, DELETE, UPDATE, and INSERT on MERGE tables. You must have SELECT, DELETE, and UPDATE privileges on the MyISAM tables that you map to a MERGE table.


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.

Use of DROP TABLE with a MERGE table drops only the MERGE specification. The underlying tables are not affected.

To create a MERGE table, you must specify a UNION=(list-of-tables) option that indicates which MyISAM tables to use. You can optionally specify an INSERT_METHOD option to control how inserts into the MERGE table take place. Use a value of FIRST or LAST to cause inserts to be made in the first or last underlying table, respectively. If you specify no INSERT_METHOD option or if you specify it with a value of NO, inserts into the MERGE table are not permitted and attempts to do so result in an error.

The following example shows how to create a MERGE table:

mysql> CREATE TABLE t1 (
    ->    message CHAR(20)) ENGINE=MyISAM;
mysql> CREATE TABLE t2 (
    ->    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 (
    ->    message CHAR(20), INDEX(a))

The older term TYPE is supported as a synonym for ENGINE for backward compatibility, but ENGINE is the preferred term and TYPE is deprecated.

Column a 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. (Similarly, a column with a UNIQUE index in the underlying tables should be indexed in the MERGE table but not as a UNIQUE index.)

After creating the MERGE table, you can use it to 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:

  • DROP the MERGE table and re-create it.

  • Use ALTER TABLE tbl_name UNION=(...) to change the list of underlying tables.

    Beginning with MySQL 5.0.60, it is also possible to use ALTER TABLE ... UNION=() (that is, with an empty UNION clause) to remove all of the underlying tables. However, in this case, the table is effectively empty and inserts fail because there is no underlying table to take new rows. Such a table might be useful as a template for creating new MERGE tables with CREATE TABLE ... LIKE.

As of MySQL 5.0.36, the underlying table definitions and indexes must conform more closely than previously to the definition of the MERGE table. Conformance is checked when a table that is part of a MERGE table is opened, not when the MERGE table is created. If any table fails the conformance checks, the operation that triggered the opening of the table fails. This means that changes to the definitions of tables within a MERGE may cause a failure when the MERGE table is accessed. The conformance checks applied to each table are:

  • The underlying table and the MERGE table must have the same number of columns.

  • The column order in the underlying table and the MERGE table must match.

  • Additionally, the specification for each corresponding column in the parent MERGE table and the underlying tables are compared and must satisfy these checks:

    • The column type in the underlying table and the MERGE table must be equal.

    • The column length in the underlying table and the MERGE table must be equal.

    • The column of the underlying table and the MERGE table can be NULL.

  • The underlying table must have at least as many indexes as the MERGE table. The underlying table may have more indexes than the MERGE table, but cannot have fewer.


    A known issue exists where indexes on the same columns must be in identical order, in both the MERGE table and the underlying MyISAM table. See Bug #33653.

    Each index must satisfy these checks:

    • The index type of the underlying table and the MERGE table must be the same.

    • The number of index parts (that is, multiple columns within a compound index) in the index definition for the underlying table and the MERGE table must be the same.

    • For each index part:

      • Index part lengths must be equal.

      • Index part types must be equal.

      • Index part languages must be equal.

      • Check whether index parts can be NULL.

For information about the table checks applied prior to MySQL 5.0.36, see Section 14.3.2, “MERGE Table Problems”.

As of MySQL 5.0.44, if a MERGE table cannot be opened or used because of a problem with an underlying table, CHECK TABLE displays information about which table caused the problem.

Additional Resources

Download this Manual
User Comments
  Posted by Edwin DeSouza on July 26, 2010
  Posted by Mustali Kachwala on August 25, 2011
  Posted by Marek Grossman on December 11, 2012
Re to: Edwin DeSouza
> Using MySQL Partitioning Instead of MERGE Tables
A Partitioning and the Merge tables are not the same!
You can decide to be better use the Merge for better performance.

For example:
Merge - seaching the underlayed tables in order as tables are in UNION list definition, when find needed record stop searching.
IMHO it's good for two tables : actual and archive data TBs.

Partitioning - can effectively "cut off" not relevant "underlayed" files - not searching thru all data files - only if SELECT-WHERE condition is with column whitch is used in PARTITION BY RANGE condition.

Before You decide to use Merge or Partitioning read some comparsion.
Sign Up Login You must be logged in to post a comment.