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 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 differentMERGE
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 aMERGE
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 ofMyISAM
tables is not.You can create an alias or synonym for a
MyISAM
table by defining aMERGE
table that maps to that single table. There should be no really notable performance impact from doing this (only a couple of indirect calls andmemcpy()
calls for each read).
The disadvantages of MERGE
tables are:
You can use only identical
MyISAM
tables for aMERGE
table.Some
MyISAM
features are unavailable inMERGE
tables. For example, you cannot createFULLTEXT
indexes onMERGE
tables. (You can createFULLTEXT
indexes on the underlyingMyISAM
tables, but you cannot search theMERGE
table with a full-text search.)If the
MERGE
table is nontemporary, all underlyingMyISAM
tables must be nontemporary. If theMERGE
table is temporary, theMyISAM
tables can be any mix of temporary and nontemporary.MERGE
tables use more file descriptors thanMyISAM
tables. If 10 clients are using aMERGE
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, theMERGE
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 makesMERGE
indexes much slower oneq_ref
searches, but not much slower onref
searches. For more information abouteq_ref
andref
, see Section 15.8.2, “EXPLAIN Statement”.