What is the MERGE statement good for?
With version 3.23.25, mysql introduced MERGE tables.
A MERGE table is a collection of identical MyISAM tables that can be used as one. You can only SELECT, DELETE, and UPDATE from the collection of tables. If you DROP the MERGE table, you are only dropping the MERGE specification.
In order to be able to be MERGEd, the involved tables have to have identical column and key information. You cannot merge tables in which the columns are packed differently, doesn't have exactly the same columns, or have the keys in different order. Using myisampack on some of the tables is ok.
Some benefits of using MERGE:
- sometimes you can restrict your search on one or two of the individual tables (faster search)
- in mysql each table corresponds to a file in the file system. Instead of having one very big file you'll have 4 files representing 4 tables. If something goes wrong (corrupted), you'll need to restore only one of the 4 files.
- You can use MERGE to create an alias/synonym for a table by just using MERGE over this one table without any notable performance
REPLACE doesn't work on MERGE tables.
The official documentation is here: