How to create table partition in MySQL?

What is table partition in MySQL?

When working with a large table, table partitioning divides the table into several subsets of data based on the given criteria and helps the database system to process one partition instead of scanning the whole table. That helps with the performance matter.

In MySQL, there are four partitioning types:

Range partitioning distributes rows to partitions based on column values falling within a given range.
List partitioning allows you to to be explicit about how rows are distributed across the partitions by specifying a list of value for the partitioning column of each partition.
Hash partitioning evenly distributed data across all partitions using a hash value of a partitioning key.
Key partitioning is similar to Hash partitioning but instead of using a user-defined expression, it uses its own internal hashing function.

Syntax

You can use the PARTITION BY clause included in CREATE TABLE statement to create a partitioned table with data distributed among one or more partitions.

Here is the generic syntax to create table partition in MySQL:

CREATE TABLE table_name 
  table_definition
PARTITION BY partition_type ([column | expression])
  partition_definition
;

Specifically,

1. To create a range partitioned table:

CREATE TABLE table_name 
  table_definition
PARTITION BY RANGE {(expr) | COLUMNS(column_list)} (
  PARTITION [partition_name_1] VALUES LESS THAN (value_1),
  PARTITION [partition_name_2] VALUES LESS THAN (value_2),
  [...]
);

Example

CREATE TABLE employees (
  id INT NOT NULL,
  first_name VARCHAR(30),
  last_name VARCHAR(30),
  hired DATE NOT NULL DEFAULT '2019-09-27',
  separated DATE NOT NULL DEFAULT '9999-12-31',
  job_code INT,
  store_id INT
)
PARTITION BY RANGE (store_id) (
  PARTITION p0 VALUES LESS THAN (6),
  PARTITION p1 VALUES LESS THAN (11),
  PARTITION p2 VALUES LESS THAN (16),
  PARTITION p3 VALUES LESS THAN MAXVALUE
);

To use multiple columns in range partitioning keys:

CREATE TABLE simple_num (
  a INT,
  b INT
)
PARTITION BY RANGE COLUMNS(a, b) (
  PARTITION p0 VALUES LESS THAN (5, 12),
  PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

2. To create a list partitioned table:

CREATE TABLE table_name 
  table_definition
PARTITION BY LIST {(expr) | COLUMNS(column_list)} (
  PARTITION [partition_name_1] VALUES IN (value_list_1)
  PARTITION [partition_name_2] VALUES IN (value_list_2)
  [...]
);

Example

CREATE TABLE customers (
  first_name VARCHAR(25),
  last_name VARCHAR(25),
  street_1 VARCHAR(30),
  street_2 VARCHAR(30),
  city VARCHAR(15),
  renewal DATE
)
PARTITION BY LIST COLUMNS(city) (
  PARTITION pRegion_1 VALUES IN('Camau', 'Kiengiang', 'Longan'),
  PARTITION pRegion_2 VALUES IN('Dalat', 'Daklak', 'Pleiku'),
  PARTITION pRegion_3 VALUES IN('Danang', 'Hoian', 'Hue'),
  PARTITION pRegion_4 VALUES IN('Hanoi', 'Sapa', 'Ninhbinh')
);

3. To create a hash partitioned table

CREATE TABLE table_name 
  table_definition
PARTITION BY HASH (expression)
PARTITION num
;

With num is a positive integer representing the number of partitions into which the table is to be divided.

Example

CREATE TABLE employees (
  id INT NOT NULL,
  first_name VARCHAR(30),
  last_name VARCHAR(30),
  hired DATE NOT NULL DEFAULT '2019-09-27',
  separated DATE NOT NULL DEFAULT '9999-12-31',
  job_code INT,
  store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

4. To create a key partitioned table:

CREATE TABLE table_name 
  table_definition
PARTITION BY KEY(column_list)
PARTITION num
;

KEY partitioning takes only a list of zero or more column names. Any columns used as the partitioning key must be a part of the primary key. When no partitioning key is defined, the primary key will be used.

In case there’s no primary key, a NOT NULL unique key will be used instead. Otherwise, the statement will fail.

Example:

CREATE TABLE simple_customer (
  id INT NOT NULL PRIMARY KEY,
  full_name VARCHAR(40)
)
PARTITION BY KEY()
PARTITIONS 2;

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.