16.7 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 5.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.

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
Chapter 20, 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.

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

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:

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

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, CHECK
TABLE displays information about which table caused the
problem.

Re to: Edwin DeSouza> Using MySQL Partitioning Instead of MERGE TablesA 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.