MERGE Tables

MERGE tables are a clever way to get around operating-system restrictions on maximum file sizes. Because each MyISAM table is stored in a single file, tables are limited in size by the maximum file size of the operating system. We get around this by creating a MERGE table, a construct that allows you to treat multiple MyISAM tables as a single table for the purpose of queries.

Let's look at an example. Code to create a MERGE table consisting of three log tables is shown in Listing 9.1.

Listing 9.1 A MERGE Table Example

 create database logs;  use logs; create table log2003Jan (logid int auto_increment primary key, logts datetime, entry char(255)); insert into log2003Jan values (NULL, '2003-01-01', 'first jan entry'); create table log2003Feb (logid int auto_increment primary key, logts datetime, entry char(255)); insert into log2003Feb values (NULL, '2003-02-01', 'first feb entry');  create table log2003Mar (logid int auto_increment primary key, logts datetime, entry char(255)); insert into log2003Mar values (NULL, '2003-03-01', 'first mar entry'); create table logs (logid int auto_increment primary key, logts datetime, entry char(255)) type = merge union = (log2003Jan, log2003Feb, log2003Mar) insert_method = last; 

What we have done in this listing is to create three tables that are identical in structure, log2003Jan, log2003Feb, and log2003Mar. Logging is a common application of MERGE tables, as you will see in a moment.

After inserting some test data into these three tables, we have created a MERGE table of the three. We have done this by creating a table called logs that has the same structure as the three tables to be merged and by specifying that it is of type MERGE and that it is the UNION of the three tables. We also specify the INSERT_METHOD as last. This means that if we insert data into the MERGE table, it will be added to the last table in the merge, in this case log2003Mar. The other options are FIRST (insert into the first table in the list) or NO (don't allow inserts into the MERGE table).

This gives us a table we can interact with that appears to contain all the data in the merged tables. If we run the query

 select * from logs; 

we will obtain the following output:

 +-------+---------------------+-----------------+  logid  logts                entry            +-------+---------------------+-----------------+      1  2003-01-01 00:00:00  first jan entry       1  2003-02-01 00:00:00  first feb entry       1  2003-03-01 00:00:00  first mar entry  +-------+---------------------+-----------------+ 3 rows in set (0.01 sec) 

As you can see, all the data from the three tables is represented. One really important thing to note is that although we specified logid as the primary key in the MERGE table, it is a bit different from the way primary keys normally work. Usually, they must be unique, but because the MERGE table manages three sets of primary keys, there may well be more than one row with the same primary key, as in the preceding output.

Even with the MERGE table, we can still query the component tables as usual. We cannot DROP , ALTER , DELETE FROM TABLE , REPAIR , TRUNCATE , OPTIMIZE , or ANALYZE any of the component tables. You will be able to do some of these things ( DELETE FROM TABLE ) if the MERGE table is not currently open . You can close it with FLUSH TABLES . The manual says that you should be able to do any of these things after a FLUSH , but we have found that this is sometimes not the case. For example, at the time of writing, dropping one of the component tables leads to the MERGE table also being silently dropped. If you need to make these sorts of changes, you may be better off dropping the MERGE table and re-creating it. Dropping the MERGE table does not affect the component tables or their data.

You can compress individual tables in the MERGE with myisampack. This is particularly useful for examples like ours, in which we are storing logfiles ”we can compress the earlier months' log files because we are writing to only the most recent log.