What is MySQL Partitions and its Types?

This article was published as a part of the Data Science Blogathon.

Introduction

In today’s data-driven world, organisations work with massive datasets and leverage some aspects of this data for their day-to-day operations. Data professionals in such companies prefer to have small partitions of data as it allows them to analyse and manipulate information without any hassle. An advanced DBMS (Database Management Systems) like MySQL provides unique features to facilitate data partitioning. One such feature is MySQL Partitions, and it allows users to split their massive data chunks into manageable segments.

This post will introduce you to MySQL Partitioning and list its various types. It will elaborate on five key MySQL Partitions and provide their syntax and examples. Read along to understand the different types of MySQL Partitions and implement them for your business today!

What is MySQL Partitioning?

Image Source

MySQL Partitioning allows you to split up tabular data into smaller individual tables stored at separate locations. This process distributes segments of the original data according to specific rules of the file system to optimise the storage. However, from the user’s point of view, there is no partition, and the table resides in a single location only.

The division of data works on specific rules called Partition Function. MySQL offers various Partition Functions, out of which HASH, RANGE, LIST, etc., are the most popular. The chosen function intakes a parameter of your choice and segments the table according to your requirements. MySQL efficiently utilises the storage space and minimises the Query Processing latency. The decrease in search time occurs because the SQL Query Engine has to search for your data in a table of diminished size.

Different Types of MySQL Partitions

This section will elaborate on the syntax and examples of the 5 popular MySQL Partitions, namely, RANGE, LIST, COLUMNS, HASH, and KEY Partitions. All of these Partitions serve different purposes, and depending upon the type of your data, you should choose the most suitable option among them.

RANGE Partitioning

The RANGE Partitions operate on column values of your tabular data, and it utilises these values to divide rows and place them in separate partitions. This MySQL Partition leverages the VALUES LESS THAN operator to identify the column data ranges in ascending order. Furthermore, this partition requires your column entries to be contiguous and non-overlapping.

You can understand more about the RANGE Partition from the following example, which divides a sales data table. The syntax required for using RANGE Partition in this situation is as follows:

CREATE TABLE Sales ( cust_id INT NOT NULL, name VARCHAR(40),

store_id VARCHAR(20) NOT NULL, bill_no INT NOT NULL,

bill_date DATE PRIMARY KEY NOT NULL, Amount DECIMAL(8,2) NOT NULL)

PARTITION BY RANGE (year(bill_date))(

PARTITION p0 VALUES LESS THAN (2016),

PARTITION p1 VALUES LESS THAN (2017),

PARTITION p2 VALUES LESS THAN (2018),

PARTITION p3 VALUES LESS THAN (2020));

Now, fill the table with adequate data using SQL Queries and print the table with the following:

SELECT * FROM Sales;

The created table is shown in the below image.

Image Source – https://www.javatpoint.com/mysql-partitioning

Now, to generate the data after partition, execute the following code:

SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH

FROM INFORMATION_SCHEMA.PARTITIONS

WHERE TABLE_SCHEMA = ‘myemployeedb’ AND TABLE_NAME = ‘Sales’;

The results of the RANGE Partition are shown in the below image.

Image Source – https://hevodata.com/learn/mysql-partitions/#T1

 LIST Partitioning

LIST Partitions, like RANGE Partitions, also depend on column values to decide the table partition. However, this MySQL Partition goes further and implements column matching using discrete values to choose the data split. The LIST Partition deploys the VALUES IN statement to identify the matching criteria.

The LIST Partition provides a comprehensive data split, but you have to manually decide the locations where it will store the data after partitioning. You can understand the syntax of LIST Partition from the following example:

CREATE TABLE Stores (

cust_name VARCHAR(40),

bill_no VARCHAR(20) NOT NULL,

store_id INT PRIMARY KEY NOT NULL,

bill_date DATE NOT NULL,

amount DECIMAL(8,2) NOT NULL

)

PARTITION BY LIST(store_id) (

PARTITION pEast VALUES IN (101, 103, 105),

PARTITION pWest VALUES IN (102, 104, 106),

PARTITION pNorth VALUES IN (107, 109, 111),

PARTITION pSouth VALUES IN (108, 110, 112));

The above code partitions a table containing records of a store’s sales. The result from using this RANGE Partition is shown in the below image.

Image Source – https://hevodata.com/learn/mysql-partitions/#T4

COLUMNS Partitioning

This form of MySQL Partition involves the usage of multiple table columns as partitioning keys. The COLUMN Partition is further classified into the following 2 categories:

Range Columns

RANGE COLUMNS Partitioning provides similar functionality as the RANGE Partition. However, it also allows you to input multiple columns at once to work as the Primary Key. Furthermore, it is not limited to integers and will enable you to work with other data types.

The following syntax is useful for partitioning tables using the RANGE COLUMNS Partition:

CREATE TABLE table_name

PARTITIONED BY RANGE COLUMNS(column_list) (

PARTITION partition_name VALUES LESS THAN (value_list)[,

PARTITION partition_name VALUES LESS THAN (value_list)][,

…]

)

column_list:

column_name[, column_name][, …]

value_list:

value[, value][, …]

Using the above syntax on a data table will generate the following result.

Image Source – https://www.javatpoint.com/mysql-partitioning

List COLUMNS Partitioning

The LIST COLUMNS Partitioning provides the same benefits as the RANGE COLUMNS Partitioning, and it also allows you to use advanced data types such as String, DATE, DATETIME, and much more. This MySQL Partition relies on a list of columns to work as the Primary Key required for the data split.

The following example deploys a List Columns Partitioning to optimally organize agent data:

CREATE TABLE AgentDetail (

agent_id VARCHAR(10),

agent_name VARCHAR(40),

city VARCHAR(10))

PARTITION BY LIST COLUMNS(agent_id) (

PARTITION pNewyork VALUES IN(‘A1’, ‘A2’, ‘A3’),

PARTITION pTexas VALUES IN(‘B1’, ‘B2’, ‘B3’),

PARTITION pCalifornia VALUES IN (‘C1’, ‘C2’, ‘C3’));

The above code can split an Agent Database table and provide the results shown below.

Image Source – https://www.javatpoint.com/mysql-partitioning

HASH Partitioning

The HASH Partition operates using a user-defined expression. It would be best to input the table’s entry in an MYSQL expression that uses the returned values to partition the table. The only constraint on your MySQL expression is that it should return a non-negative integer.

The HASH-based MySQL Partitions are ideal if you seek to split data among smaller multiple partitions. The advantage of this partition is that it safeguards you from the manual task of defining data storage locations. HASH Partition uses the INT value of any selected column to decide the post-split storage locations. The following example presents the syntax required for HASH Partitions.

CREATE TABLE Stores (

cust_name VARCHAR(40),

bill_no VARCHAR(20) NOT NULL,

store_id INT PRIMARY KEY NOT NULL,

bill_date DATE NOT NULL,

amount DECIMAL(8,2) NOT NULL

)

PARTITION BY HASH(store_id)

PARTITIONS 4;

KEY Partitioning

The KEY Partition has a functionality similar to the HASH Partitions as both of them rely on MySQL expressions to devise data splits. However, in KEY Partition, the required expression is introduced by MySQL itself.

You can use KEY Partition to split a table with non-integer values. This is possible because KEY Partition’s MySQL expression will always return an integer value independent of the column’s data. You can further understand the working of this MySQL Partition using the following example:

CREATE TABLE AgentDetail (

agent_id INT NOT NULL PRIMARY KEY,

agent_name VARCHAR(40)

)

PARTITION BY KEY()

PARTITIONS 2;

In a situation where the table contains a unique key but has no primary key, you can leverage the UNIQUE KEY to perform the required partition using the below code:

CREATE TABLE AgentDetail (

agent_id INT NOT NULL UNIQUE KEY,

agent_name VARCHAR(40)

)

PARTITION BY KEY()

PARTITIONS 2;

You can read more about MySQL Partitioning, its advantages, and its limitations on Hevo’s blog.

Conclusion

In this article, we had discussed the following:

• Data Partitioning

• How you can Leverage MySQL Partition Mechanisms

• Various Partition Mechanisms, their Syntax, Use cases, and Practical Examples.

Each MySQL Partition, RANGE, HASH, LINE, COLUMN, and KEY houses its ideal use cases and limitations. Hence, it’s essential to understand your data requirements and make an informed choice while partitioning your data.

Partitioning your MySQL Data can be beneficial when dealing with a massive dataset, and it can help you boost query performance and ensure higher scalability & better throughput. However, before your leverage Partitioning, be careful about their limitations, such as no support for Full-text-based indexes, absence of Foreign keys, etc.

MySQL Partitions may help you optimally utilise vast volumes of data; however, as your business grows, setting up a Data Warehouse as your Sign Source of Truth is the ideal way to go. This implies leveraging a Data Pipeline to transfer data from MySQL to your Warehouse. This is where Hevo comes into the picture. Hevo Data provides a No-code Data Pipeline to automate your data transfer process from 100+ Data Sources to your desired data warehouses.

Read the latest articles on our blog.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.