Databases Reference
In-Depth Information
table leaves its “child” tables untouched, but dropping one of the child tables has a
different effect, which is operating system-specific. On GNU/Linux, for example, the
underlying table's file descriptor stays open and the table continues to exist, but only
via the merge table:
mysql>
DROP TABLE t1, t2;
mysql>
SELECT a FROM mrg;
+------+
| a |
+------+
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
+------+
A variety of other limitations and special behaviors exist. Here are some aspects of
merge tables you should keep in mind:
• The
CREATE
statement that creates a merge table doesn't check that the underlying
tables are compatible. If the underlying tables are defined slightly differently,
MySQL might 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.”
•
REPLACE
doesn't work at all on a merge table, and
AUTO_INCREMENT
won't work as
you might expect. We'll let you read the manual for the details.
• 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, rel-
ative 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 underlying
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
state-
ment. If you frequently need data in a specific order, you can exploit this to
make the merge-sorting operation faster.
