The way MySQL implements merge tables has some important performance implica-
tions. As with any other MySQL feature, this makes them better suited for some uses
than others. Here are some aspects of merge tables you should keep in mind:
• A merge table requires more open file descriptors than a non-merge table con-
taining the same data. Even though a merge table looks like a single table, it
actually opens the underlying tables separately. As a result, a single table cache
entry can create many file descriptors. Therefore, even if you have configured the
table cache to protect your server against exceeding the operating system’s per-
process file-descriptor limits, merge tables can cause you to exceed that limit
anyway.
The CREATE statement that creates a merge table doesn’t check that the underly-
ing tables are compatible. If the underlying tables are defined slightly differently,
MySQL may create a merge table that it can’t use later. Also, if you alter one of
the underlying tables after creating a valid merge table, it will stop working and
you’ll see this error: “ERROR 1168 (HY000): Unable to open underlying table
which is differently defined or of non-MyISAM type or doesn’t exist.”
• Queries that access a merge table access every underlying table. This can make
single-row key lookups relatively slow, compared to a lookup in a single table.
Therefore, it’s a good idea to limit the number of underlying tables in a merge
table, especially if it is the second or later table in a join. The less data you access
with each operation, the more important the cost of accessing each table
becomes, relative to the entire operation. Here are a few things to keep in mind
when planning how to use merge tables:
• Range lookups are less affected by the overhead of accessing all the underly-
ing tables than individual item lookups.
• Table scans are just as fast on merge tables as they are on normal tables.
• Unique key and primary key lookups stop as soon as they succeed. In this
case, the server accesses the underlying merge tables one at a time until the
lookup finds a value, and then it accesses no further tables.
• The underlying tables are read in the order specified in the CREATE TABLE
statement. If you frequently need data in a specific order, you can exploit
this to make the merge-sorting operation faster.
That’s not the only use for merge tables, though. They’re used frequently in data
warehousing applications, because another strength is the way they help manage
huge volumes of data. It’s practically impossible to manage a single table with tera-
bytes of data, but the task is much easier if it’s just a merged collection of 50 GB
tables.
Because merge tables don’t hide the underlying MyISAM tables, they offer some fea-
tures that partitions don’t:
• A MyISAM table can be a member of many merge tables.
• You can copy underlying tables between servers by copying the .frm, .MYI, and
.MYD files.
• You can add more tables to a merge collection easily; just create a new table and
alter the merge definition.
• You can create temporary merge tables that include only the data you want, such
as data from a specific time period, which you can’t do with partitions.
• You can remove a table from the merge if you want to back it up, restore it, alter
it, repair it, or perform other operations on it. You can then add it back when
you’re done.
• You can use myisampack to compress some or all of the underlying tables.
In contrast, a partitioned table’s partitions are hidden by the MySQL server and are
accessible only through the partitioned table.

Comments are closed.