Vertical Partitioning for MySQL

Registered 2009-06-03 by Kentoku SHIBA

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"';

Project information

Maintainer:
Kentoku SHIBA
Driver:
Not yet selected
Development focus:

trunk series 

Programming Languages:
C++
Licences:
GNU GPL v2
()

RDF metadata

View full history Series and milestones

Vertical Partitioning for MySQL trunk series is the current focus of development

Get Involved

  • Report a bug
  • warning
    Ask a question
  • warning
    Help translate

Downloads

Latest version is 1.1-for-5.5.34-10.0.9
released on 2014-03-23

All downloads