15.7 The MERGE Storage Engine

Menu:
merge-table-advantages:: MERGE Table Advantages and Disadvantages
merge-table-problems:: MERGE Table Problems

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 data types and index information. You cannot merge 'MyISAM' tables in which the columns are listed in a different order, do not have exactly the same data types in corresponding columns, or have the indexes in different order. However, any or all of the 'MyISAM' tables can be compressed with note 'myisampack': myisampack. See note myisampack::. Differences between tables such as these do not matter:

Names of corresponding columns and indexes can differ.
Comments for tables, columns, and indexes can differ.
Table options such as 'AVG_ROW_LENGTH', 'MAX_ROWS', or 'PACK_KEYS' can differ.

An alternative to a 'MERGE' table is a partitioned table, which stores partitions of a single table in separate files. Partitioning enables some operations to be performed more efficiently and is not limited to the 'MyISAM' storage engine. For more information, see *note partitioning::.

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 note 'SELECT': select, note 'DELETE': delete, note 'UPDATE': update, and note 'INSERT': insert. on 'MERGE' tables. You must have 'SELECT', 'DELETE', and 'UPDATE' 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.

Use of *note 'DROP TABLE': 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 (
     ->    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;

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.

It is also possible to use 'ALTER TABLE ... UNION=()' (that is, with an empty note 'UNION': 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 note 'CREATE TABLE ... LIKE': create-table-like.

The underlying table definitions and indexes must conform closely 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.

Note:

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'.

If a 'MERGE' table cannot be opened or used because of a problem with an underlying table, *note 'CHECK TABLE': check-table. displays information about which table caused the problem.

Additional Resources

A forum dedicated to the 'MERGE' storage engine is available at https://forums.mysql.com/list.php?93.

File: manual.info.tmp, Node: merge-table-advantages, Next: merge-table-problems, Prev: merge-storage-engine, Up: merge-storage-engine

15.7.1 MERGE Table Advantages and Disadvantages

'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 *note 'myisampack': myisampack, and then create a 'MERGE' table to use them as one.
Obtain more speed. You can split a large read-only table based on some criteria, and then put individual tables on different disks. A 'MERGE' table structured this way could be much faster than using a single large table.
Perform more efficient searches. If you know exactly what you are looking for, you can search in just one of the underlying 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 smaller 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. (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 can instead create a 'MERGE' table from 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.
Some 'MyISAM' features are unavailable in 'MERGE' tables. For example, you cannot create 'FULLTEXT' indexes on 'MERGE' tables. (You can 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 nontemporary, all underlying 'MyISAM' tables must be nontemporary. If the 'MERGE' table is temporary, the 'MyISAM' tables can be any mix of temporary and nontemporary.
'MERGE' tables use more file descriptors than 'MyISAM' tables. If 10 clients are using a 'MERGE' table that maps to 10 tables, the server uses (10 x 10) + 10 file descriptors. (10 data file descriptors for each of the 10 clients, and 10 index file descriptors shared among the clients.)
Index reads are slower. When you read an index, the 'MERGE' storage engine needs to issue a read on all underlying tables to check which one most closely matches a given index value. To read the next index value, the 'MERGE' storage engine needs to search the read buffers to find the next value. Only when one index buffer is used up does the storage engine need to read the next index block. This makes 'MERGE' indexes much slower on 'eq_ref' searches, but not much slower on 'ref' searches. For more information about 'eq_ref' and 'ref', see *note explain::.

File: manual.info.tmp, Node: merge-table-problems, Prev: merge-table-advantages, Up: merge-storage-engine

15.7.2 MERGE Table Problems

The following are known problems with 'MERGE' tables:

'MERGE' child tables are locked through the parent table. If the parent is a temporary table, it is not locked, and thus the child tables are also not locked; this means that parallel use of the underlying *note 'MyISAM': myisam-storage-engine. tables corrupts them.
If you use *note 'ALTER TABLE': alter-table. to change a 'MERGE' table to another storage engine, the mapping to the underlying tables is lost. Instead, the rows from the underlying 'MyISAM' tables are copied into the altered table, which then uses the specified storage engine.
The 'INSERT_METHOD' table option for a 'MERGE' table indicates which underlying 'MyISAM' table to use for inserts into the 'MERGE' table. However, use of the 'AUTO_INCREMENT' table option for that 'MyISAM' table has no effect for inserts into the 'MERGE' table until at least one row has been inserted directly into the 'MyISAM' table.
A 'MERGE' table cannot maintain uniqueness constraints over the entire table. When you perform an *note 'INSERT': insert, the data goes into the first or last 'MyISAM' table (as determined by the 'INSERT_METHOD' option). MySQL ensures that unique key values remain unique within that 'MyISAM' table, but not over all the underlying tables in the collection.

Because the 'MERGE' engine cannot enforce uniqueness over the set of underlying tables, *note 'REPLACE': replace. does not work as expected. The two key facts are:

* *note 'REPLACE': replace. can detect unique key violations
  only in the underlying table to which it is going to write
  (which is determined by the 'INSERT_METHOD' option).  This
  differs from violations in the 'MERGE' table itself.

* If *note 'REPLACE': replace. detects a unique key violation,
  it changes only the corresponding row in the underlying table
  it is writing to; that is, the first or last table, as
  determined by the 'INSERT_METHOD' option.

Similar considerations apply for *note 'INSERT ... ON DUPLICATE KEY UPDATE': insert-on-duplicate.

'MERGE' tables do not support partitioning. That is, you cannot partition a 'MERGE' table, nor can any of a 'MERGE' table's underlying 'MyISAM' tables be partitioned.
You should not use note 'ANALYZE TABLE': analyze-table, note 'REPAIR TABLE': repair-table, note 'OPTIMIZE TABLE': optimize-table, note 'ALTER TABLE': alter-table, note 'DROP TABLE': drop-table, note 'DELETE': delete. without a 'WHERE' clause, or *note 'TRUNCATE TABLE': truncate-table. on any of the tables that are mapped into an open 'MERGE' table. If you do so, the 'MERGE' table may still refer to the original table and yield unexpected results. To work around this problem, ensure that no 'MERGE' tables remain open by issuing a 'FLUSH TABLES' statement prior to performing any of the named operations.

The unexpected results include the possibility that the operation on the 'MERGE' table reports table corruption. If this occurs after one of the named operations on the underlying 'MyISAM' tables, the corruption message is spurious. To deal with this, issue a 'FLUSH TABLES' statement after modifying the 'MyISAM' tables.
*note 'DROP TABLE': drop-table. on a table that is in use by a 'MERGE' table does not work on Windows because the 'MERGE' storage engine's table mapping is hidden from the upper layer of MySQL. Windows does not permit open files to be deleted, so you first must flush all 'MERGE' tables (with 'FLUSH TABLES') or drop the 'MERGE' table before dropping the table.
The definition of the 'MyISAM' tables and the 'MERGE' table are checked when the tables are accessed (for example, as part of a note 'SELECT': select. or note 'INSERT': insert. statement). The checks ensure that the definitions of the tables and the parent 'MERGE' table definition match by comparing column order, types, sizes and associated indexes. If there is a difference between the tables, an error is returned and the statement fails. Because these checks take place when the tables are opened, any changes to the definition of a single table, including column changes, column ordering, and engine alterations causes the statement to fail.
The order of indexes in the 'MERGE' table and its underlying tables should be the same. If you use note 'ALTER TABLE': alter-table. to add a 'UNIQUE' index to a table used in a 'MERGE' table, and then use note 'ALTER TABLE': alter-table. to add a nonunique index on the 'MERGE' table, the index ordering is different for the tables if there was already a nonunique index in the underlying table. (This happens because *note 'ALTER TABLE': alter-table. puts 'UNIQUE' indexes before nonunique 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: 'TBL_NAME.MRG' (errno: 2)', it generally indicates that some of the underlying tables do not use the 'MyISAM' storage engine. Confirm that all of these tables are 'MyISAM'.
The maximum number of rows in a 'MERGE' table is 2^64 (~1.844E+19; the same as for a 'MyISAM' table). It is not possible to merge multiple 'MyISAM' tables into a single 'MERGE' table that would have more than this number of rows.
Use of underlying 'MyISAM' tables of differing row formats with a parent 'MERGE' table is currently known to fail. See Bug #32364.
You cannot change the union list of a nontemporary 'MERGE' table when *note 'LOCK TABLES': lock-tables. is in effect. The following does not work:
```
  CREATE TABLE m1 ... ENGINE=MRG_MYISAM ...;
  LOCK TABLES t1 WRITE, t2 WRITE, m1 WRITE;
  ALTER TABLE m1 ... UNION=(t1,t2) ...;
```
However, you can do this with a temporary 'MERGE' table.
You cannot create a 'MERGE' table with 'CREATE ... SELECT', neither as a temporary 'MERGE' table, nor as a nontemporary 'MERGE' table. For example:
```
  CREATE TABLE m1 ... ENGINE=MRG_MYISAM ... SELECT ...;
```
Attempts to do this result in an error: TBL_NAME is not 'BASE TABLE'.
In some cases, differing 'PACK_KEYS' table option values among the 'MERGE' and underlying tables cause unexpected results if the underlying tables contain 'CHAR' or 'BINARY' columns. As a workaround, use 'ALTER TABLE' to ensure that all involved tables have the same 'PACK_KEYS' value. (Bug #50646)

File: manual.info.tmp, Node: federated-storage-engine, Next: example-storage-engine, Prev: merge-storage-engine, Up: storage-engines