MERGE
Table Problems
1303
• 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
[646]
searches, but not much slower on
ref
[646]
searches. For more information about
eq_ref
[646]
and
ref
[646]
, see
Section 13.8.2,
“
EXPLAIN
Syntax”
.
14.3.2.
MERGE
Table Problems
The following are known problems with
MERGE
tables:
• If you use
ALTER TABLE
to change a
MERGE
table to another storage engine, the mapping to the
underlying tables is lost. Instead, the rows from the underlying
MyISAM
tables are copied into the
altered table, which then uses the specified storage engine.
• The
INSERT_METHOD
table option for a
MERGE
table indicates which underlying
MyISAM
table to use
for inserts into the
MERGE
table. However, use of the
AUTO_INCREMENT
table option for that
MyISAM
table has no effect for inserts into the
MERGE
table until at least one row has been inserted directly
into the
MyISAM
table.
• A
MERGE
table cannot maintain uniqueness constraints over the entire table. When you perform an
INSERT
, the data goes into the first or last
MyISAM
table (as determined by the
INSERT_METHOD
option). MySQL ensures that unique key values remain unique within that
MyISAM
table, but not over
all the underlying tables in the collection.
• Because the
MERGE
engine cannot enforce uniqueness over the set of underlying tables,
REPLACE
does not work as expected. The two key facts are:
•
REPLACE
can detect unique key violations only in the underlying table to which it is going to write
(which is determined by the
INSERT_METHOD
option). This differs from violations in the
MERGE
table itself.
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
