Partition in MySQL

Yusuf SEZER July 14, 2020 MySQL-MariaDB

In the article, the use of partition in MySQL is explained with various examples.

What is Partition in MySQL?

We can translate the partition word used in MySQL and advanced DBMS systems as division and separation.

Regardless of the normalization and index structure of the database design, as the data grows, the table will start to slow down after a while.

Partition, and distributed MySQL servers can be used to prevent slowdown.

Using the partition feature, certain parts of the data are physically divided into pieces.

For example; Let’s say we have 2000 records in the products table with product information. We can divide the table into sections, with the first 1000 records in the first section and the second 1000 records in the second section.

Similarly, we can divide the table by date by years, months and days.

The partition property is used only in columns that contain numeric data or that can be converted into numeric data.

Partition Types in MySQL

Partition types consist of four parts: RANGE, LIST, HASH and KEY.

RANGE Partititon in MySQL

It is used to partition the column by a certain range.

How To Create Range Partition in MySQL

CREATE TABLE products (
  product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  product_name VARCHAR(255) NOT NULL,
  product_price DECIMAL(10,0) NOT NULL
)
PARTITION BY RANGE(product_id)(
  PARTITION p0 VALUES LESS THAN (1000),
  PARTITION p1 VALUES LESS THAN (2000),
  PARTITION p2 VALUES LESS THAN (3000),
  PARTITION p3 VALUES LESS THAN (4000),
  PARTITION p4 VALUES LESS THAN MAXVALUE
);

CREATE TABLE products (

product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

product_name VARCHAR(255) NOT NULL,

product_price DECIMAL(10,0) NOT NULL

)

PARTITION BY RANGE(product_id)(

PARTITION p0 VALUES LESS THAN (1000),

PARTITION p1 VALUES LESS THAN (2000),

PARTITION p2 VALUES LESS THAN (3000),

PARTITION p3 VALUES LESS THAN (4000),

PARTITION p4 VALUES LESS THAN MAXVALUE

);

You can access information about the partitions created with the following commands.

EXPLAIN PARTITIONS SELECT * FROM products;

1	EXPLAIN PARTITIONS SELECT * FROM products;

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'products';

1	SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'products';

LIST Partition in MySQL

It is used to divide the column by specific value.

How To Create List Partition in MySQL

CREATE TABLE products (
  product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  product_name VARCHAR(255) NOT NULL,
  product_price DECIMAL(10,0) NOT NULL
)
PARTITION BY LIST(product_id)(
  PARTITION p0 VALUES IN(1, 2, 5),
  PARTITION p1 VALUES IN(3, 4),
  PARTITION p2 VALUES IN(9, 6),
  PARTITION p3 VALUES IN(7, 8)
);

CREATE TABLE products (

product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

product_name VARCHAR(255) NOT NULL,

product_price DECIMAL(10,0) NOT NULL

)

PARTITION BY LIST(product_id)(

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

PARTITION p1 VALUES IN(3, 4),

PARTITION p2 VALUES IN(9, 6),

PARTITION p3 VALUES IN(7, 8)

);

As a result of the command, in the p0 section, the values of product_id value 1, 2 and 5 will be stored. p1, p2, p3 values will also store the values written in parentheses.

HASH and KEY Partitions in MySQL

It performs partitioning according to special algorithmic expression.

If KEY is selected as the partition type, it processes based on the PRIMARY KEY or UNIQUE KEY in the table.

How To Create HASH and KEY Partitions in MySQL

DROP TABLE IF EXISTS products;
CREATE TABLE IF NOT EXISTS products (
  product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  product_name VARCHAR(255) NOT NULL,
  product_price DECIMAL(10,0) NOT NULL
)
PARTITION BY KEY()
PARTITIONS 5;

DROP TABLE IF EXISTS products;

CREATE TABLE IF NOT EXISTS products (

product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

product_name VARCHAR(255) NOT NULL,

product_price DECIMAL(10,0) NOT NULL

)

PARTITION BY KEY()

PARTITIONS 5;

If there is no PRIMARY KEY or UNIQUE KEY in the table, it will throw an error.

Similarly, partitioning can be done by selecting PRIMARY KEY or UNIQUE KEY column.

DROP TABLE IF EXISTS products;
CREATE TABLE IF NOT EXISTS products (
  product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  product_name VARCHAR(255) NOT NULL,
  product_price DECIMAL(10,0) NOT NULL
)
PARTITION BY HASH(product_id)
PARTITIONS 5;

DROP TABLE IF EXISTS products;

CREATE TABLE IF NOT EXISTS products (

product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

product_name VARCHAR(255) NOT NULL,

product_price DECIMAL(10,0) NOT NULL

)

PARTITION BY HASH(product_id)

PARTITIONS 5;

The HASH partition type is created similar to the KEY partition type.

However, the processes performed in the background are different.

Although partitioning is done, if a good query is not created, there may not be an increase in performance.

You can select partition in query expressions to increase performance.

In the example below, in the p0 section, that is, in the section with the first 1000 records, we query the values with the value of product_id below 250.

SELECT * FROM products PARTITION (p0) WHERE product_id < 250;

1	SELECT * FROM products PARTITION (p0) WHERE product_id < 250;

Similarly, it will be effective to write the column name that is partitioned as the first search criterion in queries.

SELECT * FROM products WHERE product_id < 1000 AND product_name = 'MySQL Books';

1	SELECT * FROM products WHERE product_id < 1000 AND product_name = 'MySQL Books';

The most appropriate partition type should be selected by performing performance tests after partitioning.

Also, ANALYZE, OPTIMIZE, CHECK, REPAIR are used as optimization commands for partitioning.

I wish you good day.

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

About Yusuf SEZER

Leave a Reply Cancel reply