MERGE Table Problems •If the  MERGE  table is nontemporary, all underlying  MyISAM  tables must be nontemporary. If the  MERGE table is temporary, the  MyISAM  tables can be any mix of temporary and nontemporary. •  MERGE  tables use more file descriptors than  MyISAM  tables. If 10 clients are using a  MERGE  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.) •Index reads are slower. When you read an index, the  MERGE  storage engine needs to issue a read on all underlying tables to check which one most closely matches a given index value. To read the next index value, the  MERGE  storage engine needs to search the read buffers to find the next value. Only when one index buffer is used up does the storage engine need to read the next index block. This makes  MERGE indexes much slower on  eq_ref  searches, but not much slower on  ref  searches. For more information about  eq_ref  and  ref , see Section 13.8.2, “EXPLAIN Statement” . •In versions of MySQL Server prior to 5.1.23, it was possible to create temporary merge tables with nontemporary child MyISAM tables. •If you use  ALTER TABLE  to change a  MERGE  table to another storage engine, the mapping to the underlying tables is lost. Instead, the rows from the underlying  MyISAM  tables are copied into the altered table, which then uses the specified storage engine. •The  INSERT_METHOD  table option for a  MERGE  table indicates which underlying  MyISAM  table to use for inserts into the  MERGE  table. However, use of the  AUTO_INCREMENT  table option for that  MyISAM  table has no effect for inserts into the  MERGE  table until at least one row has been inserted directly into the MyISAM  table. •A  MERGE  table cannot maintain uniqueness constraints over the entire table. When you perform an INSERT , the data goes into the first or last  MyISAM  table (as determined by the  INSERT_METHOD option). MySQL ensures that unique key values remain unique within that  MyISAM  table, but not over all the underlying tables in the collection. •Because the  MERGE  engine cannot enforce uniqueness over the set of underlying tables,  REPLACE  does not work as expected. The two key facts are: • REPLACE  can detect unique key violations only in the underlying table to which it is going to write (which is determined by the  INSERT_METHOD  option). This differs from violations in the  MERGE  table itself. •If  REPLACE  detects a unique key violation, it changes only the corresponding row in the underlying table it is writing to; that is, the first or last table, as determined by the  INSERT_METHOD  option. • MERGE  tables do not support partitioning. That is, you cannot partition a  MERGE  table, nor can any of a MERGE  table’s underlying  MyISAM  tables be partitioned. •You should not use  ANALYZE TABLE ,  REPAIR TABLE ,  OPTIMIZE TABLE ,  ALTER TABLE ,  DROP TABLE ,  DELETE  without a  WHERE  clause, or  TRUNCATE TABLE  on any of the tables that are mapped 3424 Similar considerations apply for  INSERT ... ON DUPLICATE KEY UPDATE . From versions 5.1.23, MERGE children were locked through the parent table. If the parent was temporary, it was not locked and so the children were not locked either. Parallel use of the MyISAM tables corrupted them. The following are known problems with  MERGE  tables: 16.7.2 MERGE Table Problems