The vertical partitioning storage engine merges tables for vertical partitioning and joins by primary key.
You can use Vertical Partitioning Storage Engine like following samples.
--- Performance tuning sample ---
(select col_d from tbl_a)
Before partition
create table tbl_a(
col_a int primary key,
col_b int,
col_c int,
col_d int,
col_e varchar(255),
col_f text,
key idx_a(col_b, col_c),
key idx_b(col_c, col_f(100))
)engine=InnoDB;
After partition
create table tbl_b(
col_a int primary key,
col_b int,
col_c int,
col_d int,
key idx_a(col_b, col_c)
)engine=InnoDB;
create table tbl_c(
col_a int primary key,
col_c int,
col_e varchar(255),
col_f text,
key idx_b(col_c, col_f(100))
)engine=InnoDB;
create table tbl_a(
col_a int primary key,
col_b int,
col_c int,
col_d int,
col_e varchar(255),
col_f text,
key idx_a(col_b, col_c),
key idx_b(col_c, col_f(100))
)engine=VP
comment 'table_name_list "tbl_b tbl_c"';
--- Mixed storage engine sample ---
create table tbl_b(
col_a int primary key,
col_b int,
col_c int,
key idx_a(col_b, col_c)
)engine=ndb ......;
create table tbl_c(
col_a int primary key,
col_c int,
col_d varchar(255),
col_e text,
key idx_b(col_c, col_e(100))
)engine=Spider ......;
create table tbl_a(
col_a int primary key,
col_b int,
col_c int,
col_d varchar(255),
col_e text,
key idx_a(col_b, col_c),
key idx_b(col_c, col_e(100))
)engine=VP
comment 'table_name_list "tbl_b tbl_c"';
--- Multi shaped partitioning sample ---
Before partition
create table employees (
id int primary key,
fname varchar(30),
lname varchar(30),
hired date not null default '1970-01-01',
separated date not null default '9999-12-31',
job_code int,
store_id int
)engine=InnoDB;
After partition
create table emp_pk (
id int not null,
separated date not null default '9999-12-31',
unique idx_a(id),
key idx_b(id, separated)
)engine=InnoDB
partition by hash(id)
partitions 4;
create table emp_detail (
id int not null,
fname varchar(30),
lname varchar(30),
hired date not null default '1970-01-01',
separated date not null default '9999-12-31',
job_code int,
store_id int,
key idx_a(id, separated)
)engine=InnoDB
partition by range ( year(separated) ) (
partition p0 values less than (1991),
partition p1 values less than (1996),
partition p2 values less than (2001),
partition p3 values less than maxvalue
);
create table employees (
id int not null,
fname varchar(30),
lname varchar(30),
hired date not null default '1970-01-01',
separated date not null default '9999-12-31',
job_code int,
store_id int,
primary key(id, separated)
)engine=VP
comment 'table_name_list "emp_pk emp_detail", pk_correspond_mode "1"';
View full history Series and milestones
trunk series is the current focus of development.