Source code editor What Is Ajax
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 Section 8.7, “myisampack — Generate Compressed, Read-Only MyISAM Tables”. Differences in table options such as
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.
Starting with MySQL 5.0.36 the underlying table definitions and indexes must conform more closely to the definition of the
MERGE table. Conformance will be checked when the merged tables are opened, not when the
MERGE table is created. This means that changes to the definitions of tables within a
MERGE may cause a failure when the
MERGE table is accessed.
You can use
MERGE tables. You must have
DELETE privileges on the
MyISAM tables that you map to a
The use of
MERGE tables entails the following security issue: If a user has access to
t, that user can create a
m that accesses
t. However, if the user's privileges on
t are subsequently revoked, the user can continue to access
t by doing so through
m. If this behavior is undesirable, you can start the server with the new
--skip-merge option to disable the
MERGE storage engine. This option is available as of MySQL 5.0.24.
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=( 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
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
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.
In MySQL 5.0.36 and higher, when a table that is part of a
MERGE table is opened, the following checks are applied before opening each table. If any table fails the conformance checks, then the operation that triggered the opening of the table will fail. The conformance checks applied to each table are:
Table must have exactly the same amount of columns that
MERGE table has.
Column order in the
MERGE table must match the column order in the underlying tables.
Additionally, the specification for each column in the parent
MERGE table and the underlying table are compared. For each column, MySQL checks:
Column type in the underlying table equals the column type of
Column length in the underlying table equals the column length of
Column of underlying table and column of
MERGE table can be
Underlying table must have at least the same amount of keys that merge table has. The underlying table may have morekeys than the
MERGE table, but cannot have less.
For each key:
Check if the key type of underlying table equals the key type of merge table.
Check if number of key parts (i.e. multiple columns within a compound key) in the underlying table key definition equals the number of key parts in merge table key definition.
For each key part:
Check if key part lengths are equal.
Check if key part types are equal.
Check if key part languages are equal.
Check if key part can be
After creating the
MERGE table, you can issue queries that operate on the group of tables as a whole:
SELECT * FROM total;+---+---------+ | a | message | +---+---------+ | 1 | Testing | | 2 | table | | 3 | t1 | | 1 | Testing | | 2 | table | | 3 | t2 | +---+---------+
To remap a
MERGE table to a different collection of
MyISAM tables, you can use one of the following methods:
MERGE table and re-create it.
ALTER TABLE to change the list of underlying tables.
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
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.)
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 Section 7.2.1, “Optimizing Queries with
EXPLAIN”, for more information about
A forum dedicated to the
MERGE storage engine is available at http://forums.mysql.com/list.php?93.
The following are known problems with
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.
REPLACE does not work.
You cannot use
DELETE without a
TRUNCATE TABLE, or
ANALYZE TABLE on any of the tables that are mapped into an open
MERGE table. If you do so, the
MERGE table may still refer to the original table, which yields unexpected results. The easiest way to work around this deficiency is to ensure that no
MERGE tables remain open by issuing a
FLUSH TABLES statement prior to performing any of those operations.
The unexpected results include the possibility that the operation on the
MERGE table will report table corruption. However, if this occurs after operations on the underlying
MyISAM tables such as those listed in the previous paragraph (
OPTIMIZE TABLE, and so forth), the corruption message is spurious. To deal with this, issue a
FLUSH TABLES statement after modifying the
DROP TABLE on a table that is in use by a
MERGE table does not work on Windows because the
MERGE storage engine's table mapping is hidden from the upper layer of MySQL. Windows does not allow open files to be deleted, so you first must flush all
MERGE tables (with
FLUSH TABLES) or drop the
MERGE table before dropping the table.
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 (depending on the value of the
INSERT_METHOD option). MySQL ensures that unique key values remain unique within that
MyISAM table, but not across all the tables in the collection.
In MySQL 5.0.36 and later, the definition of the
MyISAM tables and the
MERGE table are checked when the tables are accessed (for example, as part of a
INSERT statement). The checks ensure that the definitions of the tables and the parent
MERGE table definition match by comparing column order, types, sizes and associated indexes. If there is a difference between the tables then an error will be returned and the statement will fail.
Because these checks take place when the tables are opened, any changes to the definition of a single, including column changes, ocolumn ordering and engine alterations will cause the statement to fail.
In MySQL 5.0.35 and earlier:
When you create or alter
MERGE table, there is no check to ensure that the underlying tables are existing
MyISAM tables and have identical structures. When the
MERGE table is used, MySQL checks that the row length for all mapped tables is equal, but this is not foolproof. If you create a
MERGE table from dissimilar
MyISAM tables, you are very likely to run into strange problems.
Similarly, if you create a
MERGE table from non-
MyISAM tables, or if you drop an underlying table or alter it to be a non-
MyISAM table, no error for the
MERGE table occurs until later when you attempt to use it.
Because the underlying
MyISAM tables need not exist when the
MERGE table is created, you can create the tables in any order, as long as you do not use the
MERGE table until all of its underlying tables are in place. Also, if you can ensure that a
MERGE table will not be used during a given period, you can perform maintenance operations on the underlying tables, such as backing up or restoring them, altering them, or dropping and recreating them. It is not necessary to redefine the
MERGE table temporarily to exclude the underlying tables while you are operating on them.
The order of indexes in the
MERGE table and its underlying tables should be the same. If you use
ALTER TABLE to add a
UNIQUE index to a table used in a
MERGE table, and then use
ALTER TABLE to add a non-unique index on the
MERGE table, the index ordering is different for the tables if there was already a non-unique index in the underlying table. (This happens because
ALTER TABLE puts
UNIQUE indexes before non-unique indexes to facilitate rapid detection of duplicate keys.) Consequently, queries on tables with such indexes may return unexpected results.
If you encounter an error message similar to
ERROR 1017 (HY000): Can't find file: ' it generally indicates that some of the base tables are not using the MyISAM storage engine. Confirm that all tables are MyISAM.
mm.MRG' (errno: 2)
There is a limit of 232 (~4.295E+09)) rows to a
MERGE table, just as there is with a
MyISAM, it is therefore not possible to merge multiple
MyISAM tables that exceed this limitation. However, you build MySQL with the
--with-big-tables option then the row limitation is increased to (232)2 (1.844E+19) rows. See Section 188.8.131.52, “Typical configure Options”. Beginning with MySQL 5.0.4 all standard binaries are built with this option.
MERGE storage engine does not support
INSERT DELAYED statements.
As of MySQL 5.0.44, if a
MERGE table cannot be opened or used because of a problem with an underlying table,
CHECK TABLE displays information about which table caused the problem.
Source code editor What Is Ajax