MySQL Table Partitioning

  • This page assumes the innodb storage engine. Partitioning is also available in the NDB storage engine (but not all syntax on this page is supported for NDB - refer to the documentation).
  • Partitioning applies to all data and indexes of a table.
  • All columns used in the table's partitioning expression must be part of every unique key that the table may have, including any primary key.

Check

SELECT PARTITION_NAME, TABLE_ROWS

  FROM INFORMATION_SCHEMA.PARTITIONS

 WHERE TABLE_NAME = 'mytable';

RANGE

CREATE TABLE mytable (

   myid    INT     NOT NULL,

   mydate  DATE    NOT NULL,

   mygroup INT     NOT NULL,

   mychar  CHAR(3) NOT NULL

)

PARTITION BY RANGE (mygroup) (

   PARTITION p0 VALUES LESS THAN (5),

   PARTITION p1 VALUES LESS THAN (10),

   PARTITION p2 VALUES LESS THAN (15),

   PARTITION p3 VALUES LESS THAN (20)

);

CREATE TABLE mytable (

   myid    INT     NOT NULL,

   mydate  DATE    NOT NULL,

   mygroup INT     NOT NULL,

   mychar  CHAR(3) NOT NULL

)

PARTITION BY RANGE ( YEAR(mydate) ) (

   PARTITION p0 VALUES LESS THAN (2000),

   PARTITION p1 VALUES LESS THAN (2010),

   PARTITION p2 VALUES LESS THAN (2020),

   PARTITION p3 VALUES LESS THAN MAXVALUE

);

LIST

CREATE TABLE mytable (

   myid    INT     NOT NULL,

   mydate  DATE    NOT NULL,

   mygroup INT     NOT NULL,

   mychar  CHAR(3) NOT NULL

)

PARTITION BY LIST (mygroup) (

   PARTITION p0 VALUES IN (1,2,3,4,5),

   PARTITION p1 VALUES IN (6,7,8,9,10),

   PARTITION p2 VALUES IN (11,12,13,14,15),

   PARTITION p3 VALUES IN (16,17,18,19,20)

);

COLUMNS

CREATE TABLE mytable (

   myid    INT     NOT NULL,

   mydate  DATE    NOT NULL,

   mygroup INT     NOT NULL,

   mychar  CHAR(3) NOT NULL

)

PARTITION BY RANGE COLUMNS (myid, mygroup, mychar) (

   PARTITION p0 VALUES LESS THAN (5,5,'eee'),

   PARTITION p1 VALUES LESS THAN (10,10,'jjj'),

   PARTITION p2 VALUES LESS THAN (15,15,'ooo'),

   PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)

);

CREATE TABLE mytable (

   myid    INT     NOT NULL,

   mydate  DATE    NOT NULL,

   mygroup INT     NOT NULL,

   mychar  CHAR(3) NOT NULL

)

PARTITION BY RANGE COLUMNS ( mydate ) (

   PARTITION p0 VALUES LESS THAN ('2000-01-03'),

   PARTITION p1 VALUES LESS THAN ('2000-01-05'),

   PARTITION p2 VALUES LESS THAN ('2000-01-07'),

   PARTITION p3 VALUES LESS THAN ('2000-01-09')

);

CREATE TABLE mytable (

   myid    INT     NOT NULL,

   mydate  DATE    NOT NULL,

   mygroup INT     NOT NULL,

   mychar  CHAR(3) NOT NULL

)

PARTITION BY LIST COLUMNS (mychar) (

   PARTITION p0 VALUES IN ('aaa','bbb','ccc','ddd','eee'),

   PARTITION p1 VALUES IN ('fff','ggg','hhh','iii','jjj'),

   PARTITION p2 VALUES IN ('kkk','lll','mmm','nnn','ooo'),

   PARTITION p3 VALUES IN ('ppp','qqq','rrr','sss','ttt')

);

CREATE TABLE mytable (

   myid    INT     NOT NULL,

   mydate  DATE    NOT NULL,

   mygroup INT     NOT NULL,

   mychar  CHAR(3) NOT NULL

)

PARTITION BY LIST COLUMNS ( mydate ) (

   PARTITION p0 VALUES IN ('2000-01-01','2000-01-02'),

   PARTITION p1 VALUES IN ('2000-01-03','2000-01-04'),

   PARTITION p2 VALUES IN ('2000-01-05','2000-01-06'),

   PARTITION p3 VALUES IN ('2000-01-07','2000-01-08')

);

HASH

CREATE TABLE mytable (

   myid    INT     NOT NULL,

   mydate  DATE    NOT NULL,

   mygroup INT     NOT NULL,

   mychar  CHAR(3) NOT NULL

)

PARTITION BY HASH (mygroup) 

PARTITIONS 5;

CREATE TABLE mytable (

   myid    INT     NOT NULL,

   mydate  DATE    NOT NULL,

   mygroup INT     NOT NULL,

   mychar  CHAR(3) NOT NULL

)

PARTITION BY HASH ( YEAR(mydate) )

PARTITIONS 5;

Linear Hash

CREATE TABLE mytable (

   myid    INT     NOT NULL,

   mydate  DATE    NOT NULL,

   mygroup INT     NOT NULL,

   mychar  CHAR(3) NOT NULL

)

PARTITION BY LINEAR HASH ( YEAR(mydate) )

PARTITIONS 5;

KEY

CREATE TABLE mytable (

   myid    INT     NOT NULL PRIMARY KEY,

   mydate  DATE    NOT NULL,

   mygroup INT     NOT NULL,

   mychar  CHAR(3) NOT NULL

)

PARTITION BY KEY()

PARTITIONS 5;

CREATE TABLE mytable (

   myid    INT     NOT NULL,

   mydate  DATE    NOT NULL,

   mygroup INT     NOT NULL,

   mychar  CHAR(3) NOT NULL,

   UNIQUE KEY (mychar)

)

PARTITION BY KEY(mychar)

PARTITIONS 5;

CREATE TABLE mytable (

   myid    INT     NOT NULL PRIMARY KEY,

   mydate  DATE    NOT NULL,

   mygroup INT     NOT NULL,

   mychar  CHAR(3) NOT NULL

)

PARTITION BY LINEAR KEY()

PARTITIONS 5;

Subpartitions

CREATE TABLE mytable (

   myid    INT     NOT NULL,

   mydate  DATE    NOT NULL,

   mygroup INT     NOT NULL,

   mychar  CHAR(3) NOT NULL

)

PARTITION BY RANGE( YEAR(mydate) )

SUBPARTITION BY HASH( TO_DAYS(mydate) )

(

   PARTITION p0 VALUES LESS THAN (2000)

   (

      SUBPARTITION s0,

      SUBPARTITION s1

   ),

   PARTITION p1 VALUES LESS THAN (2010)

   (

      SUBPARTITION s2,

      SUBPARTITION s3

   ),

   PARTITION p2 VALUES LESS THAN (2020)

   (

      SUBPARTITION s4,

      SUBPARTITION s5

   )

);

Repartition

CREATE TABLE mytable (

   myid    INT     NOT NULL PRIMARY KEY,

   mydate  DATE    NOT NULL,

   mygroup INT     NOT NULL,

   mychar  CHAR(3) NOT NULL

)

PARTITION BY LIST (myid) (

   PARTITION p0 VALUES IN (1,2,3,4,5),

   PARTITION p1 VALUES IN (6,7,8,9,10),

   PARTITION p2 VALUES IN (11,12,13,14,15),

   PARTITION p3 VALUES IN (16,17,18,19,20)

);

ALTER TABLE mytable

PARTITION BY KEY(myid)

PARTITIONS 5;

Add Partition

CREATE TABLE mytable (

   myid    INT     NOT NULL PRIMARY KEY,

   mydate  DATE    NOT NULL,

   mygroup INT     NOT NULL,

   mychar  CHAR(3) NOT NULL

)

PARTITION BY LIST (myid) (

   PARTITION p0 VALUES IN (1,2,3,4,5),

   PARTITION p1 VALUES IN (6,7,8,9,10),

   PARTITION p2 VALUES IN (11,12,13,14,15),

   PARTITION p3 VALUES IN (16,17,18,19,20)

);

ALTER TABLE mytable

ADD PARTITION  (

   PARTITION p4 VALUES IN (21,22,23,24,25)

);

Bibliography