MySQL - Storage Engine - MERGE

The MERGE Engine:

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

CREATE TABLE NACountry SELECT Code, Name FROM Country WHERE Continent = 'North America';
CREATE TABLE SACountry SELECT Code, Name FROM Country WHERE Continent = 'South America';
CREATE TABLE NorthAndSouth
(
    Code CHAR(3) NOT NULL,
    Name CHAR(52) NOT NULL
) ENGINE = MERGE UNION = (NACountry, SACountry);
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License