MySQL Native Horizontal Sharding with MERGE Tables

Introduction

In the previous chapter we discussed vertical data splitting; this article continues the discussion by focusing on horizontal splitting, which operates at the row level and is largely transparent to applications.

Main Content

Horizontal Splitting in Relational Databases

Horizontal splitting can be implemented in two ways: horizontal table sharding and horizontal partitioning.

1. Horizontal Table Sharding

Horizontal table sharding divides a single table into multiple tables based on a chosen column (e.g., ID). After sharding, operations on the original table must be redirected to the appropriate child tables, typically using a global routing table.

Example: Table A is split by ID into odd‑ID table A1 and even‑ID table A2.

2. Horizontal Partitioning

Horizontal partitioning is a built‑in database feature that also splits data based on a column but cannot span multiple instances, which limits its applicability for distributed systems. It is suitable for single‑machine scenarios.

MySQL Native Horizontal Sharding

MySQL provides a native mechanism for horizontal sharding through MERGE tables (formerly used for MyISAM tables). Although MERGE tables are considered legacy, they illustrate useful concepts for sharding InnoDB tables.

Advantages of MERGE Tables

Simplifies management of large log tables by aggregating many child tables under a single logical table.

Reduces disk I/O by distributing data across multiple disks.

Provides simple query routing; a query against the MERGE table is automatically routed to the appropriate child tables.

Zero maintenance overhead; the MERGE table only stores metadata about its children.

Child tables can reside in different databases, offering flexible placement.

Example Usage

Assume ten child tables m1 – m10 , each containing 10,000 rows. The child table definition:

(debian-ytt1:3500)|(ytt)>show create table m1\G
*************************** 1. row ***************************
       Table: m1
Create Table: CREATE TABLE `m1` (
  `id` int NOT NULL,
  `r1` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_r1` (`r1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The corresponding MERGE table:

(debian-ytt1:3500)|(ytt)>show create table m_global\G
*************************** 1. row ***************************
       Table: m_global
Create Table: CREATE TABLE `m_global` (
  `id` int NOT NULL,
  `r1` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_r1` (`r1`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci INSERT_METHOD=LAST UNION=(`m1`,`m2`,`m3`,`m4`,`m5`,`m6`,`m7`,`m8`,`m9`,`m10`)

Querying the total row count across all child tables:

(debian-ytt1:3500)|(ytt)>select count(*) from m_global;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+

MERGE tables also simplify selective queries:

(debian-ytt1:3500)|(ytt)>select * from m_global where id in (1,10001,20001);
+-------+------+
| id    | r1   |
+-------+------+
| 1     | 1    |
| 10001 | 1    |
| 20001 | 1    |
+-------+------+

Limitations of MERGE Tables

Child tables must use the MyISAM engine and cannot have full‑text indexes built on the MERGE table.

MERGE tables consume more file descriptors.

Index reads can be slower because the engine scans each child table's index to find matches.

INSERT behavior is controlled by INSERT_METHOD (NO/FIRST/LAST). Using LAST inserts into the last child table, which may lead to uneven data distribution; NO makes the MERGE table read‑only.

DELETE operations affect only the MERGE table metadata, not the underlying child tables.

Performance comparison shows that a UNION of individual tables can be faster than a MERGE table query for certain workloads.

Suitable Scenarios for MERGE Tables

Log tables that are pre‑partitioned by date, user ID, etc.

Tables that are rarely updated and can be compressed.

Data with low reliability requirements, such as news or informational feeds.

Conclusion

Although MERGE tables are considered outdated, understanding their usage provides valuable insights for designing horizontal sharding strategies in MySQL.