(mysql.info.gz) MERGE storage engine

Info Catalog

(mysql.info.gz) MyISAM storage engine

(mysql.info.gz) Storage engines

(mysql.info.gz) MEMORY storage engine

 
 14.2 The `MERGE' Storage Engine
 ===============================
 

Menu

 
* MERGE table problems        `MERGE' Table Problems
 
 The `MERGE' storage engine was introduced in MySQL 3.23.25. It is also
 known as the `MRG_MyISAM' engine.
 
 A `MERGE' table 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 can't merge tables in which the
 columns are listed in a different order, don't have exactly the same
 columns, or have the indexes in different order.  However, any or all
 of the tables can be compressed with `myisampack'.   `myisampack'
 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 definition,
 and an `.MRG' file contains the names of the tables that should be used
 as one.  (Originally, all used tables had to be in the same database as
 the `MERGE' table itself. This restriction has been lifted as of MySQL
 4.1.1.)
 
 You can use `SELECT', `DELETE', `UPDATE', and (as of MySQL 4.0)
 `INSERT' on the collection of tables.  For the moment, you must have
 `SELECT', `UPDATE', and `DELETE' privileges on the 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.
 
 When you create a `MERGE' table, you must specify a
 `UNION=(list-of-tables)' clause that indicates which tables you want to
 use as one. You can optionally specify an `INSERT_METHOD' option if you
 want inserts for the `MERGE' table to happen in the first or last table
 of the `UNION' list. If you don't specify any `INSERT_METHOD' option or
 specify it with a value of `NO', attempts to insert records 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));
      mysql> CREATE TABLE t2 (
          ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
          ->    message CHAR(20));
      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))
          ->    TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
 
 Note that the `a' column is indexed in the `MERGE' table, but is not
 declared as a `PRIMARY KEY' as it is in the underlying `MyISAM' tables.
 This is necessary because a `MERGE' table cannot enforce uniqueness
 over the set of underlying tables.
 
 After creating the `MERGE' table, you can do things like this:
 
      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 do one of the following:
 
    * `DROP' the 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. (You can also use a RAID table to get the same kind of
      benefits.)
 
    * Do 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.
 
    * Do more efficient repairs. It's easier to repair the individual
      tables that are mapped to a `MERGE' table than to repair a single
      really big 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 that you join as a big table on demand
      or batch, you should instead create a `MERGE' table on them on
      demand.  This is much faster and will save 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 of 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.
 
    * `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.  If you then do a
      "read-next," the `MERGE' storage engine needs to search the read
      buffers to find the next key. Only when one key buffer is used up,
      the storage engine will 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' EXPLAIN. for more
      information about `eq_ref' and `ref'.
 
 

Info Catalog

(mysql.info.gz) MyISAM storage engine

(mysql.info.gz) Storage engines

(mysql.info.gz) MEMORY storage engine