`MERGE' Tables
==============

`MERGE' tables are new in MySQL Version 3.23.25. The code is still in
gamma, but should be reasonable stable.

A `MERGE' table (also known as a `MRG_MyISAM' table) is a collection of
identical `MyISAM' tables that can be used as one.  You can only
`SELECT', `DELETE', and `UPDATE' from the collection of tables.  If you
`DROP' the `MERGE' table, you are only dropping the `MERGE'
specification.

Note that `DELETE FROM merge_table' used without a `WHERE' will only
clear the mapping for the table, not delete everything in the mapped
tables. (We plan to fix this in 4.1).

With identical tables we mean that all tables are created with identical
column and key information.  You can't merge tables in which the
columns are packed differently, doesn't have exactly the same columns,
or have the keys in different order.  However, some of the tables can be
compressed with `myisampack'.  *Note `myisampack': myisampack.

When you create a `MERGE' table, you will get a `.frm' table definition
file and a `.MRG' table list file.  The `.MRG' just contains a list of
the index files (`.MYI' files) that should be used as one.  Before
4.1.1 all used tables had to be in the same database as the `MERGE'
table itself.

For the moment, you need to have `SELECT', `UPDATE', and `DELETE'
privileges on the tables you map to a `MERGE' table.

`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 files, compress some of them
     with `myisampack', and then create a `MERGE' to use these as one.

   * Give you more speed. You can split a big read-only table based on
     some criteria and then put the different table part on different
     disks.  A `MERGE' table on this could be much faster than using
     the big table. (You can, of course, also use a RAID to get the same
     kind of benefits.)

   * Do more efficient searches. If you know exactly what you are
     looking after, 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 active, with possible
     overlapping files.

   * More efficient repairs. It's easier to repair the individual files
     that are mapped to a `MERGE' file than trying to repair a really
     big file.

    fast
     to make or remap.  Note that you must specify the key definitions
     when you create a `MERGE' table!.

   * If you have a set of tables that you join to 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.

   * Go around the file-size limit for the operating system.

   * You can create an alias/synonym for a table by just using `MERGE'
     over one table. There shouldn't be any really notable performance
     impacts of doing this (only a couple of indirect calls and
     `memcpy()' calls for each read).

The disadvantages with `MERGE' tables are:

   * You can only use identical `MyISAM' tables for a `MERGE' table.

   * `REPLACE' doesn't work.

   * `MERGE' tables uses more file descriptors. If you are using a
     `MERGE' table that maps over 10 tables and 10 users are using
     this, you are using 10*10 + 10 file descriptors.  (10 datafiles
     for 10 users and 10 shared index files.)

   * Key reads are slower. When you do a read on a key, the `MERGE'
     storage engine will need 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" then the `MERGE' storage engine will need 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.  *Note EXPLAIN::.

   * You can't do `DROP TABLE', `ALTER TABLE', `DELETE FROM table_name'
     without a `WHERE' clause, `REPAIR TABLE', `TRUNCATE TABLE',
     `OPTIMIZE TABLE', or `ANALYZE TABLE' on any of the table that is
     mapped by a `MERGE' table that is "open".  If you do this, the
     `MERGE' table may still refer to the original table and you will
     get unexpected results. The easiest way to get around this
     deficiency is to issue the `FLUSH TABLES' command, ensuring no
     `MERGE' tables remain "open".

When you create a `MERGE' table you have to specify with
`UNION=(list-of-tables)' which tables you want to use as one.
Optionally you can specify with `INSERT_METHOD' if you want insert for
the `MERGE' table to happen in the first or last table in the `UNION'
list. If you don't specify `INSERT_METHOD' or specify `NO', then all
`INSERT' commands on the `MERGE' table will return an error.

The following example shows you how to use `MERGE' tables:

      ("Testing"),("table"),("t1");
     INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2");
     CREATE TABLE total (a INT NOT NULL AUTO_INCREMENT, message CHAR(20), KEY(a))
                  TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
     SELECT * FROM total;

Note that we didn't create a `UNIQUE' or `PRIMARY KEY' in the `total'
table as the key isn't going to be unique in the `total' table.

Note that you can also manipulate the `.MRG' file directly from the
outside of the MySQL server:

     shell> cd /mysql-data-directory/current-database
     shell> ls -1 t1.MYI t2.MYI > total.MRG
     shell> mysqladmin flush-tables

Now you can do things like:

     mysql> SELECT * FROM total;
     +---+---------+
     | a | message |
     +---+---------+
     | 1 | Testing |
     | 2 | table   |
     | 3 | t1      |
     | 1 | Testing |
     | 2 | table   |
     | 3 | t2      |
     +---+---------+

Note that the `a' column, though declared as `PRIMARY KEY', is not
really unique, as `MERGE' table cannot enforce uniqueness over a set of
underlying `MyISAM' tables.

To remap a `MERGE' table you can do one of the following:

   * `DROP' the table and re-create it

   * Use `ALTER TABLE table_name UNION=(...)'

   * Change the `.MRG' file and issue a `FLUSH TABLE' on the `MERGE'
     table and all underlying tables to force the storage engine to
     read the new definition file.

`MERGE' Table Problems
----------------------

The following are the known problems with `MERGE' tables:

   
     table ensures that the data are unique, but it knows nothing about
     others `MyISAM' tables.

   * `DELETE FROM merge_table' used without a `WHERE' will only clear
     the mapping for the table, not delete everything in the mapped
     tables.

   * `RENAME TABLE' on a table used in an active `MERGE' table may
     corrupt the table.  This will be fixed in MySQL 4.1.x.

    tables
     when the `MERGE' table is used, but this is not a fullproof check.

     If you use `MERGE' tables in this fashion, you are very likely to
     run into strange problems.

    for
     the tables if there was an old non-unique key in the table. This
     is because `ALTER TABLE' puts `UNIQUE' keys before normal keys to
     be able to detect duplicate keys as early as possible.

   * `DROP TABLE' on a table that is in use by a `MERGE' table will not
     work on Windows because the `MERGE' storage engine does the table
     mapping hidden from the upper layer of MySQL.  Because Windows
     doesn't allow you to drop files that are open, you first must
     flush all `MERGE' tables (with `FLUSH TABLES') or drop the `MERGE'
     table before dropping the table.  We will fix this at the same
     time we introduce `VIEW's.

[Назад] [Содержание] [Вперед]