14.3. The MERGE Storage Engine

MySQL 5.0

previous page next page

14.3. The MERGE Storage Engine

The storage engine, also known as the engine, is a collection of identical tables that can be used as one. “Identical” means that all tables have identical column and index information. You cannot merge tables in which the columns are listed in a different order, do not have exactly the same columns, or have the indexes in different order. However, any or all of the tables can be compressed with myisampack. See Section 8.5, “myisampack — Generate Compressed, Read-Only MyISAM Tables”. Differences in table options such as , , or do not matter.

When you create a table, MySQL creates two files on disk. The files have names that begin with the table name and have an extension to indicate the file type. An file stores the table format, and an file contains the names of the tables that should be used as one. The tables do not have to be in the same database as the table itself.

You can use , , , and on tables. You must have , , and privileges on the tables that you map to a table.

Note: The use of tables entails the following security issue: If a user has access to table , that user can create a table that accesses . However, if the user's privileges on are subsequently revoked, the user can continue to access by doing so through . If this behavior is undesirable, you can start the server with the new option to disable the storage engine. This option is available as of MySQL 5.0.24.

If you the table, you are dropping only the specification. The underlying tables are not affected.

To create a table, you must specify a ) clause that indicates which tables you want to use as one. You can optionally specify an option if you want inserts for the table to take place in the first or last table of the list. Use a value of or to cause inserts to be made in the first or last table, respectively. If you do not specify an option or if you specify it with a value of , attempts to insert rows into the table result in an error.

The following example shows how to create a table: 

mysql> 
    ->    
    ->    
mysql> 
    ->    
    ->    
mysql> 
mysql> 
mysql> 
    ->    
    ->    
    ->

The older term is supported as a synonym for for backward compatibility, but is the preferred term and is deprecated.

Note that the column is indexed as a in the underlying tables, but not in the table. There it is indexed but not as a because a table cannot enforce uniqueness over the set of underlying tables.

After creating the table, you can issue queries that operate on the group of tables as a whole: 

mysql> 
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table   |
| 3 | t1      |
| 1 | Testing |
| 2 | table   |
| 3 | t2      |
+---+---------+

To remap a table to a different collection of tables, you can use one of the following methods:

  • the table and re-create it.

  • Use UNION=(...) to change the list of underlying tables.

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 tables, compress some of them with myisampack, and then create a table to use them as one.

  • Obtain more speed. You can split a big read-only table based on some criteria, and then put individual tables on different disks. A table on this could be much faster than using the big table.

  • Perform more efficient searches. If you know exactly what you are looking for, you can search in just one of the split tables for some queries and use a table for others. You can even have many different tables that use overlapping sets of tables.

  • Perform more efficient repairs. It is easier to repair individual tables that are mapped to a table than to repair a single large table.

  • Instantly map many tables as one. A table need not maintain an index of its own because it uses the indexes of the individual tables. As a result, table collections are very fast to create or remap. (Note that you must still specify the index definitions when you create a table, even though no indexes are created.)

  • If you have a set of tables from which you create a large table on demand, you should instead create a table on them on demand. This is much faster and saves a lot of disk space.

  • Exceed the file size limit for the operating system. Each table is bound by this limit, but a collection of tables is not.

  • You can create an alias or synonym for a table by defining a table that maps to that single table. There should be no really notable performance impact from doing this (only a couple of indirect calls and calls for each read).

The disadvantages of tables are:

  • You can use only identical tables for a table.

  • You cannot use a number of features in tables. For example, you cannot create indexes on tables. (You can, of course, create indexes on the underlying tables, but you cannot search the table with a full-text search.)

  • If the table is non-temporary, all underlying tables must be non-temporary, too. If the table is temporary, the tables can be any mix of temporary and non-temporary.

  • tables use more file descriptors. If 10 clients are using a table that maps to 10 tables, the server uses (10 × 10) + 10 file descriptors. (10 data file descriptors for each of the 10 clients, and 10 index file descriptors shared among the clients.)

  • Key reads are slower. When you read a key, the storage engine needs to issue a read on all underlying tables to check which one most closely matches the given key. To read the next key, the storage engine needs to search the read buffers to find the next key. Only when one key buffer is used up does the storage engine need to read the next key block. This makes keys much slower on searches, but not much slower on searches. See Section 7.2.1, “Optimizing Queries with ”, for more information about and .

Additional resources

14.3.1. MERGE Table Problems

The following are known problems with tables:

  • If you use to change a table to another storage engine, the mapping to the underlying tables is lost. Instead, the rows from the underlying tables are copied into the altered table, which then uses the specified storage engine.

  • does not work.

  • You cannot use , , without a clause, , , , or on any of the tables that are mapped into an open table. If you do so, the table may still refer to the original table, which yields unexpected results. The easiest way to work around this deficiency is to ensure that no tables remain open by issuing a statement prior to performing any of those operations.

  • on a table that is in use by a table does not work on Windows because the storage engine's table mapping is hidden from the upper layer of MySQL. Windows does not allow open files to be deleted, so you first must flush all tables (with ) or drop the table before dropping the table.

  • A table cannot maintain uniqueness constraints over the entire table. When you perform an , the data goes into the first or last table (depending on the value of the option). MySQL ensures that unique key values remain unique within that table, but not across all the tables in the collection.

  • When you create or alter table, there is no check to ensure that the underlying tables are existing tables and have identical structures. When the table is used, MySQL checks that the row length for all mapped tables is equal, but this is not foolproof. If you create a table from dissimilar tables, you are very likely to run into strange problems.

    Similarly, if you create a table from non- tables, or if you drop an underlying table or alter it to be a non- table, no error for the table occurs until later when you attempt to use it.

  • The order of indexes in the table and its underlying tables should be the same. If you use to add a index to a table used in a table, and then use to add a non-unique index on the table, the index ordering is different for the tables if there was already a non-unique index in the underlying table. (This happens because puts indexes before non-unique indexes to facilitate rapid detection of duplicate keys.) Consequently, queries on tables with such indexes may return unexpected results.

  • If you encounter an error message similar to .MRG' (errno: 2) it generally indicates that some of the base tables are not using the MyISAM storage engine. Confirm that all tables are MyISAM.

  • There is a limit of 232 (~4.295E+09)) rows to a table, just as there is with a , it is therefore not possible to merge multiple tables that exceed this limitation. However, you build MySQL with the option then the row limitation is increased to (232)2 (1.844E+19) rows. See Section 2.9.2, “Typical configure Options”. Beginning with MySQL 5.0.4 all standard binaries are built with this option.

previous page start next page