14.3.1 MERGE Table Advantages and Disadvantages

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
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 × 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
Section 13.8.2, “EXPLAIN Syntax”.