Introduction

Businesses deal with massive datasets, and various teams in an organization require data in different ways and levels of detail. Moreover, data professionals in companies prefer to have small partitions of data as it allows them to analyze and manipulate information without any hassle. Advanced DBMS (Database Management Systems) like MySQL provide unique features to facilitate data partitioning. One such feature is MySQL Partitions, allowing users to split their huge 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!

MySQL Partitioning

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 optimize 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. This way, MySQL efficiently utilizes the storage space and minimizes 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.

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.

1) RANGE Partitioning

The RANGE Partitions operate on column values of your tabular data. It utilizes 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 by the below image.

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.

2) 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.

3) 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:

3.1) 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.

3.2) 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.

4) HASH Partitioning

The HASH Partition operates using a user-defined expression. You must 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;

5) 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

This post discussed the concept of MySQL Partitions and explained the popular Partition functions offered by MySQL. Moreover, it presented the various syntax and examples of the popular MySQL Partitions, including RANGE, HASH, LINE, COLUMN, and KEY Partitions. Using partitions can help your company use its databases optimally and allow your employee to focus on smaller chunks of data. However, relying on MySQL Partitions is a wise choice only when you have to work with massive data sets. This implies that MySQL Partitioning will not significantly affect your data if your tables have rows in the order of only thousands. Moreover, as your business grows, you will require a Data Warehous to store your vast datasets. This implies you will have to build an in-house Data Pipeline to transfer your MySQL data to the destination Warehouse. However, developing an in-house Pipeline requires a significant amount of time, money, and resources.

Hevo Data provides a No-code Data Pipeline to automate your data transfer process. This allows you to work on other key aspects of your business like Analytics, Customer Management, etc. This platform supports 100+ sources and transfers their data to Cloud-based Data Warehouses such as Snowflake, Google BigQuery, Amazon Redshift, Firebolt, etc. It will ensure a hassle-free experience for you.