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 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 12.7.2, “EXPLAIN
Syntax”.
User Comments
Add your own comment.