MySQL Table Splitting and Pagination Using the MERGE Engine

When a project's data grows over time, single tables can reach tens of millions of rows, leading to low query performance; therefore, splitting large tables becomes necessary.

For example, a table with 10 million rows can be divided into two tables each holding about 1 million rows, respecting MySQL's maximum storage limits per table.

First, create the two split tables:

DROP table IF EXISTS tb_member1;
create table tb_member1(
    id bigint primary key auto_increment,
    name varchar(20),
    age tinyint not null default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

DROP table IF EXISTS tb_member2;
create table tb_member2(
    id bigint primary key auto_increment,
    name varchar(20),
    age tinyint not null default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Data is then distributed using a modulo condition:

insert into tb_member1(id,name,sex) select id,name,sex from dd_user where id%2=0;
insert into tb_member2(id,name,sex) select id,name,sex from dd_user where id%2=1;

After the split, the tables contain the appropriate subsets of data.

To support pagination across the two tables, a MERGE (UNION) table is created:

DROP table IF EXISTS tb_member_all;
create table tb_member_all(
    id bigint primary key auto_increment,
    name varchar(20),
    age tinyint not null default '0'
) ENGINE=MERGE UNION=(tb_member1,tb_member2) INSERT_METHOD=LAST CHARSET=utf8 AUTO_INCREMENT=1;

Executing this statement may raise the error:

ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

To resolve the error, ensure that the underlying split tables use the MyISAM engine and that their column definitions match exactly.

The MERGE table itself stores no data; it acts as a virtual table that presents the union of tb_member1 and tb_member2 , allowing queries (including pagination) to be run against tb_member_all .

Using this unified view, pagination can be performed efficiently. The article also includes a Java code snippet (shown in the image) that demonstrates the use of id%2 for modulo-based data distribution.

Test results confirm that the MERGE table correctly aggregates data from the split tables and supports pagination queries.

The article concludes with an invitation for readers to discuss and share their thoughts, while also promoting a community group and additional resources.