MySQL - MERGE

A MERGE table is a logical collection of MyISAM tables that have the same structure. You can use this to manage the storage properties of the underlying MyISAM tables:

  • MERGE tables provide a workaround when you run up against filesystem file-size limitations for MyISAM tables. If a given MyISAM table is as large as it can be because its data or index files have reach a file-size limit, split the table into multiple smaller MyISAM tables and then create a MERGE table that groups them into a single logical table. The maximum effective size of the MERGE table is the maximum size of each constituent MyISAM table times the number of tables.
  • A MERGE table can contain a mix of compressed and uncompressed MyISAM tables. If some of the MyISAM tables contain archival data that will not be modified, we can compress them to save disk space.
  • If we use MyISAM table symlinking, we can place different tables on different physical devices, which allows their contents to be read in parallel.
  • If a large MyISAM table is split into smaller tables, you can still access the combined contents by defining a MERGE table. But you also can gain some advantages for certain operations when it is possible to access only particular individual files. A table repair is faster on a smaller table than a larger one. If you happen to have searches where you know all the records needed are contained in a particular table, you can search just that one table rather than the entire collection.
  • It is very fast to create or drop MERGE tables, so applications that need to work with dynamically changing sets of tables can easily instantiate MERGE tables on the fly as necessary.
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License