MySQL Partitioning: Overview and Examples

It is no surprise that splitting large datasets into individually manageable smaller parts is a conventional thing for data solutions nowadays. You can always address the precise section instead of wasting your resources on browsing the entire set. This not only helps you base your queries on relevant business requirements and thus makes your data management far more efficient, but also increases query performance and paves the way to better scalability.

Partitions in MySQL: A detailed introduction

But how can that be achieved technically? The answer lies in partitioning, which divides the rows of a table (a MySQL table, in our case) into multiple tables (a.k.a. partitions) according to the certain rules you set and stores them at different locations. Note that at the same time, the SQL layer treats your entire table as a single entity, so it won't make any actual difference to you as a user.

The actual rules you need to set are expressed in partitioning functions, which take user-defined parameters for an input. Afterwards, when you try to access certain data with an SQL query, the engine will look it up in the specified partitions; and that is how the query processing time will be reduced - good news for the performance.

However, note that partitioning is only helpful if you are dealing with really large quantities of rows. If we're talking mere thousands, the effect won't be that noticeable. But the bigger your data set is, the more useful partitioning is going to be.

Horizontal and vertical partitioning

There are two approaches to partitioning that can be applied to a table: horizontal and vertical partitioning.

Horizontal partitioning divides the rows of one table into multiple tables, and the number of columns is the same in each table. Physically, the table is split, but logically it is still a single entity. This approach is supported by MySQL.

Vertical partitioning is a bit more complex thing, as it divides the original table into multiple tables where the number of columns differs. In other words, some columns stay here, and the remainder goes there. As of now, this approach is not supported by MySQL.

Types of table partitioning in MySQL

Now it's time to take a look at the types of MySQL table partitioning with a few compelling examples. To make things even clearer, we'll run them in dbForge Studio for MySQL, our flagship IDE for the development and management of MySQL and MariaDB databases.

MySQL RANGE partitioning

RANGE partitioning divides rows into different partitions based on column values. Note that the values of the selected range should be contiguous and thus should not overlap. Let's see how it's done, with the VALUES LESS THAN operator defining the required ranges.

CREATE TABLE Sales (
  customer_id int NOT NULL,
  customer_name varchar(40),
  store_id varchar(20) NOT NULL,
  bill_number 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 let's fill the created table with values using INSERT INTO.

INSERT INTO Sales
  VALUES (1, 'Michael', 'S001', 101, '2015-01-02', 125.56),
  (2, 'Jim', 'S003', 103, '2015-01-25', 476.50),
  (3, 'Dwight', 'S012', 122, '2016-02-15', 335.00),
  (4, 'Andy', 'S345', 121, '2016-03-26', 787.00),
  (5, 'Pam', 'S234', 132, '2017-04-19', 678.00),
  (6, 'Karen', 'S743', 111, '2017-05-31', 864.00),
  (7, 'Toby', 'S234', 115, '2018-06-11', 762.00),
  (8, 'Oscar', 'S012', 125, '2019-07-24', 300.00),
  (9, 'Darryl', 'S456', 119, '2019-08-02', 492.20);  

Now let's check the inserted data with a SELECT query.

SELECT * FROM Sales; 

This is what we get if we run it in dbForge Studio.

MySQL LIST partitioning

LIST partitioning is somewhat similar to RANGE; however, it applies column matching with a set of discrete values in order to define how the data will be split into partitions. The matching criteria are defined with the VALUES IN statement; still, you need to specify the location where the partitioned data will be stored.

Here is an example. There are 12 stores located in different cities. You need to partition store data by these cities.

The LIST partitioning syntax for this example will be as follows.

CREATE TABLE Stores (
  customer_name varchar(40),
  bill_number 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 pPhiladelphia VALUES IN (1, 4, 5),
PARTITION pChicago VALUES IN (3, 7, 12),
PARTITION pScranton VALUES IN (6, 8, 9),
PARTITION pPittsburgh VALUES IN (2, 10, 11)
);  

MySQL HASH partitioning

HASH partitioning divides data into a predefined number of partitions using a value returned by a user-defined expression. It can be any legit MySQL expression that outputs a non-negative integer value. HASH partitioning is a good solution when you want to divide data evenly between a number of partitions. The INT value of selected columns defines the location of the required partition. Here is the syntax for the HASH partitioning, based on the previous example; but in this case, the Stores table is simply divided into four partitions.

CREATE TABLE Stores (
  customer_name varchar(40),
  bill_number 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;  

MySQL KEY partitioning

KEY partitioning is similar to HASH, the difference being that MySQL delivers its own internal hashing function. KEY partitions work with columns with non-integer values, yet they will always return integer values, no matter what the column data type is. Also note that if you don't mention any column for partition explicitly, then the primary key of your table will be treated as the partition key. Here is an example of a query with the KEY partitioning.

CREATE TABLE Stores (
  store_id int NOT NULL PRIMARY KEY,
  store_name varchar(40)
)
PARTITION BY KEY ()
PARTITIONS 2;  

And if your table has no primary key but has a unique key, the said unique key will be treated as the partition key instead.

CREATE TABLE Stores (
  store_id int NOT NULL UNIQUE KEY,
  store_name varchar(40)
)
PARTITION BY KEY ()
PARTITIONS 2;  

MySQL COLUMNS partitioning

Partition keys can take the shape of multiple columns. For instance, columns with non-integer values can be applied to define value ranges and list entries in RANGE COLUMNS and LIST COLUMNS partitioning respectively.

Let's see how it works with the LIST COLUMNS partitioning. Suppose we have stores in three cities organized in the following way.

Now we're going to use partitioning to organize these stores.

CREATE TABLE Stores (
  store_id varchar(10),
  store_name varchar(40),
  city varchar(10)
)
PARTITION BY LIST COLUMNS (store_id) (
PARTITION pAtlanta VALUES IN ('Store_1', 'Store_2', 'Store_3'),
PARTITION pHouston VALUES IN ('Store_4', 'Store_5', 'Store_6'),
PARTITION pBoston VALUES IN ('Store_7', 'Store_8', 'Store_9')
);

MySQL subpartitioning

Finally, we have subpartitioning, which means that we further divide partitions into multiple subpartitions. Here is an example.

CREATE TABLE Sales (
  id int NOT NULL PRIMARY KEY,
  customer_name varchar(40),
  purchase_date date
)
PARTITION BY RANGE (YEAR(purchased))
SUBPARTITION BY HASH (TO_DAYS(purchased))
SUBPARTITIONS 2 (
PARTITION p0 VALUES LESS THAN (2015),
PARTITION p1 VALUES LESS THAN (2020),
PARTITION p2 VALUES LESS THAN MAXVALUE
);  

Best practices and examples for effective MySQL partitioning

Now let us give you a few tips to make your introduction to MySQL partitioning easier.

We would also suggest you use the integrated Table Designer of dbForge Studio, which delivers the simplest and fastest way to construct and modify MySQL tables. With its help, you can easily create columns and manage their attributes, add comments and constraints, define indexes, customize table options, set triggers, and, of course, handle your partitioning operations.

How to create a partitioned MySQL table

This is where you should give dbForge Studio for MySQL a go since it allows creating partitioned tables of all the abovementioned types in a visual mode. It's all done rather easily.

• Select New Database Object from the Database menu
• Select the Table type from the list
• Enter a name for your new table and click Create; the Studio will open your newly created table
• Switch to the Partitioning tab, which looks as follows

This is where you can select the required partitioning type and configure the partitioning options. After that, you can click Apply Changes, and your partitioned table will be ready for work.

For a more detailed guide on creating partitioned tables in dbForge Studio, refer to the dedicated page in our documentation.

How to add a partition to an existing MySQL table

You can also add and remove partitions/subpartitions from an existing table in the Studio.

• Find the required table in Database Explorer
• Select Edit Table from the shortcut menu
• Proceed to the Partitioning tab
• Under Partitions, click Add and configure your partitions as required

For a more detailed guide on adding and removing partitions using dbForge Studio, refer to the dedicated page in our documentation.

How to truncate a partition in a MySQL table

Partitions can be truncated with the following query. Here is the general syntax; you only have to enter the table name and list the required partitions by name, separated by commas.

ALTER TABLE table_name
TRUNCATE PARTITION p1, p2, p3, ...;

How to use the RANK function with partitioning in MySQL

Another common case concerns the RANK function, which can be used to assign a rank to each row within a partition. Its syntax is as follows.

SELECT column_name
RANK() OVER (
PARTITION BY expression
ORDER BY expression [ASC|DESC]
) AS rank_column_name
from table_name;

Here, the RANK function is used in the SELECT query to get the required ranks of rows as output. The PARTITION BY part of the query will do the partition according to the expression specified in the clause. The ORDER BY part of the query will order the result set in ascending or descending order; this is required to assign ranks to rows.

How to use ROW_NUMBER with a partition

ROW_NUMBER is a window function in MySQL that is used to return the current row number within its partition. Its general syntax is as follows.

ROW_NUMBER() OVER (partition_definition order_definition);

Here, ROW_NUMBER is the function that assigns the row number, partition_definition defines the way you want the function to assign row numbers (e.g., PARTITION BY column_name will make a partition by the specified column and assign row numbers accordingly). Lastly, order_definition defines the required order of your data that you can specify using the ORDER BY clause.

How to perform table partitioning by date

MySQL partitioning is optimized for use with the TO_DAYS, YEAR, and TO_SECONDS functions. However, you can use other date and time functions that return an integer or NULL, such as WEEKDAY, DAYOFYEAR, or MONTH.

Here is an example of adding a purchase_date column to the Purchases table and partitioning it by date using the TO_DAYS function.

ALTER TABLE Purchases
PARTITION BY RANGE (TO_DAYS(purchase_date)) (
PARTITION p0 VALUES LESS THAN (TO_DAYS('2021-01-01')),
PARTITION p1 VALUES LESS THAN (TO_DAYS('2022-01-01')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2023-05-01')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2023-06-01')),
PARTITION p4 VALUES LESS THAN (TO_DAYS('2023-07-01')),
PARTITION p5 VALUES LESS THAN MAXVALUE
);

Note that partitions don't have to be even.

How to get information about the existing table partitions

You can use the SHOW PARTITIONS statement to view the partitions that exist in a table. The syntax is simple as ever.

SHOW PARTITIONS FROM TABLE table_name;

This statement similarly works with entire databases (SHOW PARTITION FROM DATABASE) and allows viewing partitions by index (SHOW PARTITION FROM INDEX).

Sharding vs partitioning: What is the difference?

Some may confuse partitioning with sharding. And indeed, these are very similar terms that deal with dividing large data sets into smaller subsets. The main difference is that partitioning groups these subsets on a single database instance, whereas sharded data can be spread across multiple machines. Otherwise, both of these terms are nearly identical, and in many cases, 'horizontal partitioning' and 'horizontal sharding' can be used synonymously.

The limitations of MySQL partitioning

MySQL partitioning has a few limitations that you should take note of.

• The maximum number of partitions for a table that doesn't use the NDB storage engine is 8192, subpartitions included. As for NDB, the said number may vary depending on the NDB Cluster version and a few other things.
• The restricted constructs include stored procedures, stored functions, loadable functions, declared variables, and variables that have been entered by the user.
• The arithmetic operators + - * are allowed, yet the result must be an integer or a NULL value. However, the operators / | & ^ << >> ~ are all not allowed in partitioning expressions.
• The InnoDB storage engine does not support foreign keys in partitioned tables.
• Partitioning implies operations with your file system; thus, the type and characteristics of your file system, the performance of your operating system, the specifics of your MySQL server can all have an impact on the overall performance.
• File names for partitions/subpartitions include generated delimiters such as #P# and #SP#. The case of these delimiters may vary.