MERGE tables can help you solve the following
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
MERGEtable 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
MERGEtable 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
MERGEtable for others. You can even have many different
MERGEtables that use overlapping sets of tables.
Perform more efficient repairs. It is easier to repair individual smaller tables that are mapped to a
MERGEtable than to repair a single large table.
Instantly map many tables as one. A
MERGEtable need not maintain an index of its own because it uses the indexes of the individual tables. As a result,
MERGEtable collections are very fast to create or remap. (You must still specify the index definitions when you create a
MERGEtable, 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
MERGEtable 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
MyISAMtable is bound by this limit, but a collection of
MyISAMtables is not.
You can create an alias or synonym for a
MyISAMtable by defining a
MERGEtable 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
MyISAMtables for a
MyISAMfeatures are unavailable in
MERGEtables. For example, you cannot create
MERGEtables. (You can create
FULLTEXTindexes on the underlying
MyISAMtables, but you cannot search the
MERGEtable with a full-text search.)
MERGEtable is nontemporary, all underlying
MyISAMtables must be nontemporary. If the
MERGEtable is temporary, the
MyISAMtables can be any mix of temporary and nontemporary.
MERGEtables use more file descriptors than
MyISAMtables. If 10 clients are using a
MERGEtable 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
MERGEstorage 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
MERGEstorage 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
MERGEindexes much slower on
eq_refsearches, but not much slower on
refsearches. For more information about
ref, see Section 13.8.2, “EXPLAIN Statement”.