If you use 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.

REPLACE does not work as expected because the MERGE engine cannot enforce uniqueness over the set of underlying tables. The two key facts are:

Similar considerations apply for INSERT ... ON DUPLICATE KEY UPDATE.

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 cannot use REPAIR TABLE, OPTIMIZE TABLE, DROP TABLE, ALTER TABLE, DELETE without a WHERE clause, TRUNCATE TABLE, or ANALYZE 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, which yields unexpected results. The easiest way to work around this deficiency is to ensure that no MERGE tables remain open by issuing a FLUSH TABLES statement prior to performing any of those operations.

The unexpected results include the possibility that the operation on the MERGE table will report table corruption. However, if this occurs after operations on the underlying MyISAM tables such as those listed in the previous paragraph (REPAIR TABLE, OPTIMIZE TABLE, and so forth), the corruption message is spurious. To deal with this, issue a FLUSH TABLES statement after modifying the MyISAM tables.

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

A MERGE table cannot maintain uniqueness constraints over the entire table. When you perform an INSERT, the data goes into the first or last MyISAM table (depending on the value of the INSERT_METHOD option). MySQL ensures that unique key values remain unique within that MyISAM table, but not across all the tables in the collection.

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.

The definition of the MyISAM tables and the MERGE table are checked when the tables are accessed (for example, as part of a SELECT or 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 then an error will be returned and the statement will fail.

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 will cause the statement to fail.

The order of indexes in the MERGE table and its underlying tables should be the same. If you use ALTER TABLE to add a UNIQUE index to a table used in a MERGE table, and then use ALTER TABLE to add a non-unique index on the MERGE table, the index ordering is different for the tables if there was already a non-unique index in the underlying table. (This happens because ALTER TABLE puts UNIQUE indexes 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 of these tables are MyISAM.

The maximum number of rows in a MERGE table is 2⁶⁴ (~1.844E+19; the same as for a MyISAM table), provided that the server was built using the --with-big-tables option. (All standard MySQL 6.0 standard binaries are built with this option; for more information, see Section 2.9.2, “Typical configure Options”.) It is not possible to merge multiple MyISAM tables into a single MERGE table that would have more than this number of rows.

The MERGE storage engine does not support INSERT DELAYED statements.

Using different underlying row formats in MyISAM tables with a parent MERGE table is currently known to fail. See Bug#32364.

Starting with MySQL 6.0.4, you cannot change the union list of a non-temporary MERGE table when 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.

Starting with MySQL 6.0.4, you cannot create a MERGE table with CREATE ... SELECT, neither as a temporary MERGE table, nor as a non-temporary MERGE table. For example:

CREATE TABLE m1 ... ENGINE=MRG_MYISAM ... SELECT ...;

Gives error message: table is not BASE TABLE.