29.3. The MERGE Engine

29.3. The MERGE Engine

The MERGE storage engine manages tables that have the following characteristics:

• A MERGE table is a collection of identically structured MyISAM tables. Each MERGE table is represented on disk by an .frm format file and an .MRG file that lists the names of the constituent MyISAM files. Both files are located in the database directory.

• Logically, a query on a MERGE table acts as a query on all the MyISAM tables of which it consists.

• A MERGE table creates a logical entity that can exceed the maximum MyISAM table size.

• MySQL manages contention between queries for MERGE table access using table-level locking (including locking of the underlying MyISAM tables). Deadlock cannot occur.

• A MERGE table is portable because the .MRG file is a text file and the MyISAM tables that it names are portable.

• The MERGE engine supports SELECT, DELETE, UPDATE, and INSERT statements. For INSERT, the CREATE TABLE statement can specify whether records should be inserted into the first or last table, or disallowed.

MERGE tables do have some disadvantages:

• They increase the number of file descriptors required because each of the underlying tables must be opened along with the MERGE table.

• It's slower to read indexes because MySQL has to search the indexes of multiple tables.

The following example demonstrates how to create a MERGE table. It creates MyISAM tables that have the same structure, and populates them with information about countries in North America and South America, respectively. From these tables, a MERGE table is created that can be used to access the combined information:

 mysql> CREATE TABLE NACountry SELECT Code, Name     -> FROM Country WHERE Continent = 'North America'; Query OK, 37 rows affected (0.01 sec) Records: 37  Duplicates: 0  Warnings: 0 mysql> CREATE TABLE SACountry SELECT Code, Name     -> FROM Country WHERE Continent = 'South America'; Query OK, 14 rows affected (0.01 sec) Records: 14  Duplicates: 0  Warnings: 0 mysql> DESCRIBE NACountry; +-------+----------+------+-----+---------+-------+ | Field | Type     | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | Code  | char(3)  | NO   |     |         |       | | Name  | char(52) | NO   |     |         |       | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> CREATE TABLE NorthAndSouth     -> (Code CHAR(3) NOT NULL, Name CHAR(52) NOT NULL)     -> ENGINE = MERGE UNION = (NACountry, SACountry); Query OK, 0 rows affected (0.01 sec) mysql> SELECT COUNT(*) FROM NACountry; +----------+ | COUNT(*) | +----------+ |       37 | +----------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(*) FROM SACountry; +----------+ | COUNT(*) | +----------+ |       14 | +----------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(*) FROM NorthAndSouth; +----------+ | COUNT(*) | +----------+ |       51 | +----------+ 1 row in set (0.00 sec) 

29.3.1. MERGE Locking Characteristics

The MERGE storage engine uses table-level locking. However, because a MERGE table is defined in terms of other tables, MERGE locking involves locks on those tables as well:

• When the MERGE engine acquires a lock for a MERGE table, it acquires a lock for all the underlying MyISAM tables. Thus, all the tables are locked together.

• The underlying MyISAM tables are read-locked when you issue a SELECT statement for a MERGE table.

• The underlying MyISAM tables are write-locked when you issue a statement that modifies a MERGE table, such as INSERT or DELETE.

• To explicitly lock a MERGE table with LOCK TABLES, it is sufficient to lock just that table. You need not lock the underlying MyISAM tables as well.