Composite Partitioning

As databases grow into millions of rows, simply dividing tables into partitions is sometimes not enough to maintain optimal performance. When data becomes highly complex — for example, involving time-based patterns and high user volumes — MySQL offers composite partitioning, also known as subpartitioning, to further enhance organization and speed.

What is Composite Partitioning?

Composite partitioning allows each partition to be split into additional subpartitions.
Think of it as “partitions inside partitions”, creating a two-level structure that provides more granular control over how data is distributed and accessed.

This approach is helpful when a single partitioning strategy (such as RANGE or LIST) cannot efficiently handle the workload alone. Subpartitioning often improves:
Query performance by reducing scanned data
Parallel processing, as MySQL can operate on multiple subpartitions simultaneously
Data distribution, especially in high-volume transactional systems

Implementing Composite Partitioning in MySQL

Let’s say we have a university exam results table.

We want to:

Partition data by year (so each year is a separate partition), and

Within each year, divide the data evenly across subpartitions for faster access.

CREATE TABLE exam_results (
   student_id INT,
   exam_year INT,
   department VARCHAR(50),
   marks DECIMAL(5,2)
)
PARTITION BY RANGE (exam_year)
SUBPARTITION BY HASH(student_id)
SUBPARTITIONS 4 (
   PARTITION p2019 VALUES LESS THAN (2020),
   PARTITION p2020 VALUES LESS THAN (2021),
   PARTITION p2021 VALUES LESS THAN (2022),
   PARTITION pmax VALUES LESS THAN MAXVALUE
);

Each PARTITION (e.g., p2020) holds results for a specific year.

Inside each partition, MySQL creates 4 subpartitions based on student_id.
This makes it easier to query both by year and by student, improving query performance and parallelism.

Use Cases for Composite Partitioning

Composite partitioning is ideal when:
Data must be grouped by time but evenly distributed within each time slice
Systems serve large, geographically distributed user bases
Workloads involve both temporal queries and high-volume key-based lookups

This method delivers a balanced combination of efficiency, scalability, and parallel performance, making it perfect for large-scale enterprise databases.