MySQL Table Splitting and Pagination Using the MERGE Engine

When a MySQL table grows to tens of millions of rows, query performance degrades, so the table should be split into several smaller tables.

First, two tables tb_member1 and tb_member2 are created (both using the MyISAM engine) with the same schema as the original table:

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 between the two tables using a modulo operation on the id column:

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, a MERGE (union) table tb_member_all is created to provide a unified view of the two partitions:

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;

If an error such as ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type occurs, verify that the underlying tables use the MyISAM engine and that their column definitions match exactly.

Because the MERGE table does not store data itself, it acts as a shell that forwards queries to the underlying tables, allowing pagination queries to be run against tb_member_all while the actual data resides in tb_member1 and tb_member2 .

Java code (shown as images in the original article) can then use the merged table for paginated retrieval, leveraging the same id%2 logic to determine which partition a record belongs to.