Working with Tables

Merge Tables

MERGE tables are essentially a virtual union of several existing MyISAM tables all of which exhibit identical column definitions.

A MERGE table composed of several tables can have some advantages over a single, large, MyISAM table, such as a higher read speed (if the tables are distributed over several hard drives) or a circumvention of the maximum file size in a number of older operating systems (for example, 2 gigabytes for Linux; 2.2 for 32-bit processors).

Among the disadvantages are that it is impossible to insert data records into MERGE tables (that is, INSERT does not function). Instead, INSERT must be applied to one of the subtables.

In the meanwhile, since most modern operating systems support files of arbitrary size, as well as RAID (that is, the division of a file system on several hard disks), MERGE tables play a subordinate role in practice.

MERGE table type, available in MySQL 3.23.25 and up, provides a way to perform queries on a set of tables simultaneously by treating them all as a single logical unit.

MERGE can be applied to a collection of MyISAM tables that all have identical structure. Suppose you have a set of individual log tables that contain log entries on a year-by-year basis and that each are defined like this, where CCYY represents the century and year:

CREATE TABLE log_CCYY ( dt DATETIME NOT NULL, info VARCHAR(100) NOT NULL, INDEX (dt) ) TYPE = MYISAM;

If the current set of log tables includes log_1999 and log_2000 you can set up a MERGE table that maps onto them like this:

CREATE TABLE log_all ( dt DATETIME NOT NULL, info VARCHAR(100) NOT NULL, INDEX (dt) ) TYPE = MERGE UNION = (log_1999, log_2000);

The TYPE option must be MERGE, and the UNION option lists the tables to be included in the MERGE table. After the table has been set up, you query it just like any other table, but the queries will refer to all the constituent tables at once.

The following query determines the total number of rows in all the log tables:

SELECT COUNT(*) FROM log_all;

This query determines how many log entries there are per year:

SELECT YEAR(dt) AS y, COUNT(*) AS entries FROM log_all GROUP BY y;

Besides the convenience of being able to refer to multiple tables without issuing multiple queries, MERGE tables offer some other nice features:

. A MERGE table can be used to create a logical entity that exceeds the allowable size of individual MyISAM tables.

. You can include compressed tables in the collection. For example, after a given year comes to an end, you wouldn't be adding any more entries to the corresponding log file, so you could compress it with myisampack to save space. The MERGE table will continue to function as before.

. Operations on MERGE tables are similar to UNION operations. UNION is unavailable prior to MySQL 4, but MERGE tables can be used in some cases as a workaround.

MERGE tables also support DELETE and UPDATE operations. INSERT is trickier, because MySQL needs to know which table to insert new records into. As of MySQL 4.0.0, MERGE table definitions can include an INSERT_METHOD option with a value of NO, FIRST, or LAST to indicate that INSERT is forbidden or that records should be inserted into the first or last table named in the UNION option.

For example, the following definition would cause an INSERT into log_all to be treated as an INSERT into log_2003, the last table named in the UNION option:

CREATE TABLE log_all ( dt DATETIME NOT NULL, info VARCHAR(100) NOT NULL, INDEX (dt) ) TYPE = MERGE UNION = (log_1999, log_2000, log_2001, log_2002, log_2003) INSERT_METHOD = LAST;