MERGE Tables
============
`MERGE' tables are new in MySQL Version 3.23.25. The code is still in
gamma, but should be resonable stable.
A `MERGE' 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.0).
With identical tables we mean that all tables are created with identical
column and key information. You can't put a MERGE over tables where the
columns are packed differently, doesn't have exactly the same columns or
have the keys in different order. Some of the tables can however be
compressed with `myisampack'. Note: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. All used
tables must 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 *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 real big
file.
* Instant mapping of many files as one. A `MERGE' table uses the
index of the individual tables. It doesn't need to maintain an
index of its one. This makes `MERGE' table collections VERY 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's for each read).
The disadvantages with `MERGE' tables are:
* You can't use `INSERT' on `MERGE' tables, as MySQL can't know in
which of the tables we should insert the row.
* You can only use identical `MyISAM' tables for a `MERGE' table.
* `MERGE' tables uses more file descriptors. If you are using a
*MERGE* that maps over 10 tables and 10 users are using this, you
are using 10*10 + 10 file descriptors. (10 data files for 10 users
and 10 shared index files.)
* Key reads are slower. When you do a read on a key, the `MERGE'
handler 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 table handler will need to search the
read buffers to find the next key. Only when one key buffer is
used up, the handler 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' or `DELETE FROM
table_name' without a `WHERE' clause 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 following example shows you how to use `MERGE' tables:
CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2");
CREATE TABLE total (a INT NOT NULL, message CHAR(20), KEY(a)) TYPE=MERGE UNION=(t1,t2);
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 |
+---+---------+
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 handler to read the
new definition file.