(mysql.info) merge-storage-engine

(mysql.info) memory-storage-engine

 
 14.3 The `MERGE' Storage Engine
 ===============================

 
* merge-table-problems         `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 
 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 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.
 
 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.
 
 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.
 
 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      |
      +---+---------+
 
 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 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.
 
    * 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.
 
    * 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  explain, for more information about
      `eq_ref' and `ref'.
 
 *Additional resources*
 
    * A forum dedicated to the `MERGE' storage engine is available at
      `http://forums.mysql.com/list.php?93'.

Info Catalog

(mysql.info) innodb

(mysql.info) storage-engines

(mysql.info) memory-storage-engine

automatically generated byinfo2html

(mysql.info) merge-storage-engine

Menu