How to Combine Multiple MyISAM Tables with MySQL’s MERGE Engine

What is the MERGE Storage Engine?

The MERGE engine lets you treat several MyISAM tables that share exactly the same schema (identical column order, names, types, and index definitions) as one logical table, simplifying queries across the combined data set.

Creating the Individual Tables

CREATE TABLE log_YY (
  dt  DATETIME NOT NULL,
  info VARCHAR(100) NOT NULL,
  INDEX (dt)
) ENGINE=MyISAM;

Assume four yearly log tables— log_2004, log_2005, log_2006, and log_2007 —are created with the definition above.

Defining the MERGE Table

CREATE TABLE log_merge (
  dt DATETIME NOT NULL,
  info VARCHAR(100) NOT NULL,
  INDEX (dt)
) ENGINE=MERGE UNION=(log_2004,log_2005,log_2006,log_2007);

The ENGINE must be MERGE, and the UNION option lists all member tables. After creation, log_merge can be queried like any regular table, with each query automatically applied to all underlying tables.

Basic Queries on the MERGE Table

SELECT COUNT(*) FROM log_merge;

This returns the total number of rows across all four yearly tables.

SELECT YEAR(dt) AS y, COUNT(*) AS entries
FROM log_merge
GROUP BY y;

This groups the rows by year, showing how many log entries each year contains.

INSERT Handling

The MERGE engine supports DELETE and UPDATE directly. INSERT is more complex because MySQL must decide which member table receives the new row. Starting with MySQL 4.0 you can specify an INSERT_METHOD option with values NO, FIRST, or LAST to control this behavior.

CREATE TABLE log_merge (
  dt DATETIME NOT NULL,
  info VARCHAR(100) NOT NULL,
  INDEX (dt)
) ENGINE=MERGE UNION=(log_2004,log_2005,log_2006,log_2007)
INSERT_METHOD=LAST;

With INSERT_METHOD=LAST, new rows are inserted into the last table listed in the UNION (here log_2007).

Adding a New Member Table

CREATE TABLE log_2009 LIKE log_2008;
ALTER TABLE log_merge
UNION = (log_2004,log_2005,log_2006,log_2007,log_2008,log_2009);

This creates a new table with the same structure and updates the MERGE definition to include it.

Important Caveat

Do not define primary keys on the member tables of a MERGE table. If two member tables contain identical primary‑key values, a query that filters by that key will only return rows from the first table in the UNION list, leading to incomplete results.