MySQL PARTITION BY Clause

Partition By clause can be used to improve query performance. It reduces storage requirements, and improves data manageability. By partitioning a large table, queries that access only a small subset of the data can be executed more quickly. Partitioning can also improve backup and restore times. In this article, we will discuss the Partition By clause in MySQL with syntax and various examples.

Introduction

The purpose of a PARTITION BY clause is to group rows of a table into separate partitions. This is particularly helpful when performing calculations on specific rows within a partition using other rows from the same partition.

The PARTITION BY clause must always be used within an OVER() clause. The partitions created by the PARTITION BY clause are also referred to as windows. This clause operates exclusively on window functions such as RANK(), LEAD(), and LAG().

If the PARTITION BY clause is excluded from the OVER() clause, the entire table is treated as a single partition.

Syntax

Window_function ( expression )
   Over ( partition by expr [order_clause] [frame_clause] )

The order_clause and frame_clause are optional components of the syntax.

In MySQL, the expression in the Partition clause can be either column names or built-in functions. However, in standard SQL, only column names are allowed within the expression.

Example

Let's take the "Hacker" table as an example −

h_id	h_name	challenge_id	score
3	Raju	111	20
2	Mithlesh	111	80
5	Rudra	112	40
5	Mohan	114	90
4	Rohan	112	30
1	Sohan	112	40

We need to determine the rank of each hacker in each challenge. In other words, we must list all hackers who participated in a challenge and their respective rank in that challenge.

To accomplish this, we use the following query:

select challenge_id, h_id, h_name, score,
dense_rank() over ( partition by challenge_id order by score desc )
as "rank", from hacker;

In this query, the partition by clause groups the table by challenge_id.

The order by clause sorts the hackers in each partition by score in descending order.

The over() clause specifies how to partition and order the rows of the table for the window function rank().

The window function dense_rank() assigns a rank to each hacker in the ordered partition of challenges. If two hackers have the same score, they are assigned the same rank.

The resulting output displays a list of all hackers along with their respective ranks in each challenge −

challenge_id	h_id	h_name	score	rank
111	2	Mithlesh	80	1
111	3	Raju	20	2
112		Rudra	40	1
112	1	Sohan	40	1
112	4	Rohan	30	2
114	5	Mohan	90	1

Therefore, we have successfully obtained a list of all hackers along with their respective ranks in each individual challenge.

Uses of the PARTITION BY Clause

• Grouping rows of a table into separate partitions for performing calculations on specific rows within a partition.

• Reducing storage requirements and improving data manageability.

• Improving query performance by executing queries that access only a small subset of the data more quickly.

• Improving backup and restore times.

Conclusion

PARTITION BY clause in MySQL is a useful tool for grouping rows of a table into separate partitions, improving query performance and reducing storage requirements. This clause operates exclusively on window functions such as RANK(), LEAD(), and LAG(). The syntax is straightforward and allows for flexibility in the types of expressions used within the clause. The example above demonstrates the functionality of the PARTITION BY clause in calculating a running total of sales for each customer. By utilizing this powerful feature, users can optimize their database performance and improve data manageability.