Skip navigation links

Additional languages
MySQL 5.0 Reference Manual :: 13 Storage Engines :: 13.3 The MERGE Storage Engine
13.3.1 MERGE Table Problems »
Section Navigation      [Toggle]

13.3. The MERGE Storage Engine

[+/-]

13.3.1. 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 Section 4.6.5, “myisampack — Generate Compressed, Read-Only MyISAM Tables”. 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.

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 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.

Note

The use of MERGE tables entails the following security issue: If a user has access to MyISAM table t, that user can create a MERGE table 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.

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.

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 MERGE table.

    • Column length in the underlying table equals the column length of MERGE table.

    • Column of underlying table and column of MERGE table can be NULL.

  • Underlying table must have at least the same amount of keys that merge table has. The underlying table may have more keys than the MERGE table, but cannot have less.

    Note

    A known issue exists that keys on the some columns must be identical in order in both the MERGE table and the underlying MyISAM table. See Bug#33653.

    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 NULL.

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      |
+---+---------+

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.

    Beginning with MySQL 5.0.60, it is also possible to use ALTER TABLE ... UNION=() (that is, with an empty UNION clause) to remove all of the underlying tables. (Bug#28248)

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 Section 12.3.2, “EXPLAIN Syntax”, for more information about eq_ref and ref.

Additional resources


User Comments

Posted by Michael Cosby on April 16 2007 5:35am[Delete] [Edit]

Here's a useful way to create temporary merge tables in code. I have a set of 10 tables I use for logs and then a permanent merge table for when I want to work with all of them at once. When accessing the last 7 days' worth of data, though, I only need to access (at most) 2 of the 10 tables, but which two changes all the time. So to create that merge table without hardcoding the table definition into my code I do:

create temporary table my_merge_table like real_table_0;
alter table my_merge_table engine=merge;
alter table my_merge_table union=(real_table_4,real_table_5);

(the only dynamic part of those statements is the table names in the last query)

Posted by ken lyons on September 3 2008 9:11pm[Delete] [Edit]

One problem I've run into with a merge table, in version 3.23, has to do with renaming a partition underneath the merge. Basically, it always or frequently corrupts the underlying table. My script ran a check before and after renaming the component table, and the first one always showed it was OK, and the second one always showed it was corrupt (always a length error on the table, readily repairable with "repair table", but that's not something one wants to do routinely). If I dropped the merge table before doing the rename, it worked fine.

Since dropping the merge table is not something I want to do in a production environment, I've settled instead on a plan to keep track of a version number for each partition, and construct a new table called partition_vn, with a new version number, vn. Then I redefine the union parameter of the merge table, to include that new version rather than the old one, and drop the old copy of that partition, and all is well.

I note this bug might not be present in later mysql versions.

Posted by Brano Gerzo on September 4 2008 9:19am[Delete] [Edit]

Michael Cosby: it is nice workaround you've posted, but when in real_table_0 is defined default charset it will not work.

Add your own comment.