MySQL Table Partitioning

Databases

MySQL

MySQL Schema

MySQL Tables

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

MySQL Forums - Partitioning

https://dev.mysql.com/doc/refman/8.0/en/partitioning.htmlhttps://dev.mysql.com/doc/refman/8.0/en/partitioning-subpartitions.htmlhttps://dev.mysql.com/doc/refman/8.0/en/partitioning-handling-nulls.htmlhttps://dev.mysql.com/doc/refman/8.0/en/partitioning-management-range-list.html
https://forums.mysql.com/list.php?106 (MySQL Partitioning Forum)