(mysql.info) merge-storage-engine
Info Catalog
(mysql.info) innodb
(mysql.info) storage-engines
(mysql.info) memory-storage-engine
14.3 The `MERGE' Storage Engine
===============================
Menu
* merge-table-problems `MERGE' Table Problems
The `MERGE' storage engine, also known as the `MRG_MyISAM' engine, is a
collection of identical `MyISAM' tables that can be used as one.
`Identical' means that all tables have identical column and index
information. You cannot merge `MyISAM' tables in which the columns are
listed in a different order, do not have exactly the same columns, or
have the indexes in different order. However, any or all of the
`MyISAM' tables can be compressed with `myisampack'. See
myisampack. Differences in table options such as `AVG_ROW_LENGTH',
`MAX_ROWS', or `PACK_KEYS' do not matter.
When you create a `MERGE' table, MySQL creates two files on disk. The
files have names that begin with the table name and have an extension
to indicate the file type. An `.frm' file stores the table format, and
an `.MRG' file contains the names of the tables that should be used as
one. The tables do not have to be in the same database as the `MERGE'
table itself.
You can use `SELECT', `DELETE', `UPDATE', and `INSERT' on `MERGE'
tables. You must have `SELECT', `UPDATE', and `DELETE' privileges on the
`MyISAM' tables that you map to a `MERGE' table.
If you `DROP' the `MERGE' table, you are dropping only the `MERGE'
specification. The underlying tables are not affected.
To create a `MERGE' table, you must specify a `UNION=(LIST-OF-TABLES)'
clause that indicates which `MyISAM' tables you want to use as one. You
can optionally specify an `INSERT_METHOD' option if you want inserts for
the `MERGE' table to take place in the first or last table of the
`UNION' list. Use a value of `FIRST' or `LAST' to cause inserts to be
made in the first or last table, respectively. If you do not specify an
`INSERT_METHOD' option or if you specify it with a value of `NO',
attempts to insert rows into the `MERGE' table result in an error.
The following example shows how to create a `MERGE' table:
mysql> CREATE TABLE t1 (
-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> message CHAR(20)) ENGINE=MyISAM;
mysql> CREATE TABLE t2 (
-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> message CHAR(20)) ENGINE=MyISAM;
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql> CREATE TABLE total (
-> a INT NOT NULL AUTO_INCREMENT,
-> message CHAR(20), INDEX(a))
-> ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
The older term `TYPE' is supported as a synonym for `ENGINE' for
backward compatibility, but `ENGINE' is the preferred term and `TYPE'
is deprecated.
Note that the `a' column is indexed as a `PRIMARY KEY' in the underlying
`MyISAM' tables, but not in the `MERGE' table. There it is indexed but
not as a `PRIMARY KEY' because a `MERGE' table cannot enforce
uniqueness over the set of underlying tables.
After creating the `MERGE' table, you can issue queries that operate on
the group of tables as a whole:
mysql> SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table |
| 3 | t1 |
| 1 | Testing |
| 2 | table |
| 3 | t2 |
+---+---------+
Note that you can also manipulate the `.MRG' file directly from outside
of the MySQL server:
shell> cd /MYSQL-DATA-DIRECTORY/CURRENT-DATABASE
shell> ls -1 t1 t2 > total.MRG
shell> mysqladmin flush-tables
To remap a `MERGE' table to a different collection of `MyISAM' tables,
you can use one of the following methods:
* `DROP' the `MERGE' table and re-create it.
* Use `ALTER TABLE TBL_NAME UNION=(...)' to change the list of
underlying tables.
* Change the `.MRG' file and issue a `FLUSH TABLE' statement for the
`MERGE' table and all underlying tables to force the storage
engine to read the new definition file.
`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 big read-only table based on
some criteria, and then put individual tables on different disks.
A `MERGE' table on this could be much faster than using the big
table.
* Perform more efficient searches. If you know exactly what you are
looking for, you can search in just one of the split 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
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. (Note that 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 should instead create a `MERGE' table on 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.
* You cannot use a number of `MyISAM' features in `MERGE' tables.
For example, you cannot create `FULLTEXT' indexes on `MERGE'
tables. (You can, of course, 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 non-temporary, all underlying `MyISAM'
tables must be non-temporary, too. If the `MERGE' table is
temporary, the `MyISAM' tables can be any mix of temporary and
non-temporary.
* `MERGE' tables use more file descriptors. 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.)
* Key reads are slower. When you read a key, the `MERGE' storage
engine needs to issue a read on all underlying tables to check
which one most closely matches the given key. To read the next
key, the `MERGE' storage engine needs to search the read buffers
to find the next key. Only when one key buffer is used up does the
storage engine need to read the next key block. This makes `MERGE'
keys much slower on `eq_ref' searches, but not much slower on
`ref' searches. See explain, for more information about
`eq_ref' and `ref'.
*Additional resources*
* A forum dedicated to the `MERGE' storage engine is available at
`http://forums.mysql.com/list.php?93'.
Info Catalog
(mysql.info) innodb
(mysql.info) storage-engines
(mysql.info) memory-storage-engine
automatically generated byinfo2html