7.2 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. See section 4.7.4 myisampack, The MySQL Compressed Read-only Table Generator.

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.
• 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() 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. See section 5.2.1 EXPLAIN Syntax (Get Information About a SELECT).
• 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:

CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
CREATE TABLE t2 (a INT NOT NULL 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 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.

Subsections

• 7.2.1 MERGE Table Problems

User Comments

<CODE>On a Win95, MySQL 4.0.0a-alpha Max server DELETE FROM merge_table Works perfectly.

CREATE DATABASE delworks ;
USE delworks ;
CREATE TABLE jan ( id INT UNSIGNED );
CREATE TABLE feb ( id INT UNSIGNED );
CREATE TABLE log ( id INT UNSIGNED ) TYPE=MERGE UNION=(jan,feb) INSERT_METHOD=LAST ;

INSERT INTO jan VALUES (1) ;
INSERT INTO feb VALUES (2) ;
DELETE FROM log;
SELECT * FROM log ; #RETURN NOTHING
SELECT * FROM feb ; #RETURN NOTHING
</CODE>
<A HREF="mailto:apichot@fr.uu.net">Antoine</A>

It looks like a merge table merge list
cannot include a (sub)table which is itself a
merge table. It would be nicer if it allowed
this...

It is possible to make MERGE tables with tables
from multiple databases on the same server. It
does take a little hackery at the moment however.
First, the tables still need to be different names
at the time the MERGE table is created. Second,
use the qualified name of the table in the
UNION=() declaration, i.e. databasename.tablename.
The MERGE table gets created, but will not be
usalble yet. You then need to hand edit the
$INSTALLDIR/var/databasename/mergetablename.MRG
file, as it will have all the table names, but no
database information. Just add the full path for
each of the tables in other databases to the
tablename. So far I have only tested doing
selects, but that seems to work fine.

One other interesting note. Just out of curiosity,
I renamed one of the tables in a different
database used in the MERGE table (so the table has
the same name as a table in the same database and
used in the MERGE table), and edited the
tablename.MRG file to reflect the move. Selects
still seem to work fine.

God only knows what side effects there might be,
but so far this has been a real boon. I have many
databases on the same server containing genomic
information. They are all from different sources,
but most share tables describing the same basic
features (protein sequence for example). HTH!

Update: As of version 3.23.51 this no longer
works :(. Not only can you not create the tables
as above, but even if you make the table another
way and change the .MRG files later, it no longer
does it's SELECTs including tables in other
databases. Hopefully there was a good reason for
this, because I liked it much better the other way!

In the example of
ALTER TABLE table_name UNION(...)

it would be clearer to include the =
ALTER TABLE table_name UNION=(...)

A mysqldump dumps all data of the merge-table. I think it would be better, only to dump the definition.

Add your own comment.