Dev Learning Lab

The labs on this site need JavaScript to run. The same content is written out below.

Learn › Database › Scaling and distribution

Partitioning

When a table is cut into pieces, which pieces does a query read?

Partitioning stores one big table in several pieces (partitions) inside the same server. You can divide it by a range, such as the year of the order date (RANGE), or by a calculation, such as the remainder of the member number divided by 4 (HASH). Depending on how you write the condition, a query reads only the partitions it needs or reads all of them.

Checked against real execution · On MySQL 8.4.11, 32 combinations were run, and the error codes and the partitions in EXPLAIN were checked against the results

Concepts in this lab

Sharding and partitioning
Partitioning divides a large table into several pieces by a criterion such as a date range and manages it that way. Sharding stores data across several database servers. It spreads out a write load or a data volume that one server cannot handle. With sharding, joins and transactions that span servers become difficult.
RANGE partitioning and HASH partitioning
The two main ways to divide a table into partitions. The column (or expression) that decides which partition a row goes to is called the partitioning key. RANGE assigns a range of values to each partition. It suits queries that look for a continuous range such as dates, and it suits dropping old data one partition at a time. A value that does not belong to any of the ranges cannot be inserted, so you must add the partition for the next period in advance. HASH decides the partition by the remainder of the partitioning key divided by the number of partitions. It spreads rows evenly, but a range condition can hardly reduce the partitions to read.
Partition pruning
An optimization that does not read a partition at all when the condition shows that the row cannot be in it. Pruning means cutting away what is not needed. It works only when the condition compares the partitioning key column as it is with a value or a range, and in MySQL you can check the partitions actually read in the partitions column of EXPLAIN. If the condition has no partitioning key, or if a function or a calculation is applied to the partitioning key column, all the partitions are read. Most of the query performance gained by partitioning comes from this pruning.
Query execution plan (EXPLAIN)
The plan that the database decides on for how to run a query. It shows which indexes are used, in what order the tables are joined, and how many rows the database expects to read. You view it by putting EXPLAIN in front of the query, and it is the starting point for finding the cause when a query is slow.
Primary key, unique key, foreign key
A primary key (PK) is the column that uniquely identifies each row of a table. Its values cannot be duplicated or empty, and a table has only one. A unique key is a unique constraint, which prevents duplicate values, and a table can have several. A foreign key (FK) is a column that references the primary key (or a unique key) of another table. It guarantees that the referenced row really exists.

Try these

Running with the default settings

  1. CREATE TABLE … PARTITION BY RANGE (YEAR(ordered_at)): The orders table is created in three pieces (p2023, p2024, p2025) according to the year of the order date. Each of these pieces is a partition. To whoever writes queries, it still looks like one orders table, and all three partitions are in the same MySQL server.
  2. 300 orders are inserted. The inserting side does not specify a partition. For each row, MySQL chooses the partition it goes into by looking at the year of the order date. Rows per partition: p2023: 121, p2024: 122, p2025: 57.
  3. INSERT … VALUES (1001, 7, '2025-06-01', 5000): The year of the order date is 2025, so the row goes into partition p2025, which accepts "less than 2026".
  4. EXPLAIN SELECT * FROM orders WHERE YEAR(ordered_at) = 2024: The condition is applied not to the ordered_at column itself but to YEAR(ordered_at). Even though this is exactly the expression the table is partitioned by, MySQL cannot pick partitions from a condition that wraps the column in a function, so it reads all three partitions. If you leave the column as it is and write a range, as in ordered_at >= '2024-01-01' AND ordered_at < '2025-01-01', it reads only partition p2024. The partitions column of EXPLAIN: p2023,p2024,p2025.

Result of this run

Code

CREATE TABLE orders (
  id BIGINT NOT NULL,
  member_id INT NOT NULL,
  ordered_at DATE NOT NULL,
  amount INT NOT NULL,
  PRIMARY KEY (id, ordered_at)
)
PARTITION BY RANGE (YEAR(ordered_at)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026)
);

-- 300 orders are already in the table, and one more is inserted
-- Column order: id, member_id, ordered_at, amount
INSERT INTO orders VALUES (1001, 7, '2025-06-01', 5000);

-- Which partitions this query reads appears in the partitions column of EXPLAIN
EXPLAIN SELECT * FROM orders WHERE YEAR(ordered_at) = 2024;

Assumptions of this model

All 32 combinations were run with the same statements as on the screen on MySQL 8.4.11 (InnoDB) on the PC used to write this lab, and checked against the screen. For each combination I created the table anew and confirmed that the following matched the screen: whether CREATE TABLE succeeded and its error code; the number of rows of the 300 orders in each partition (SELECT COUNT(*) … PARTITION (name)); the partition that the new row went into, or the error code of the INSERT; and the partitions column of EXPLAIN. The error messages written on the screen are the real messages too. I also confirmed the following facts used in the explanations by execution. Error 1503 occurs even when a unique key does not have the partitioning key. A table with neither a primary key nor a unique key is not subject to the restriction. If the date condition is written as a range on the column (>= and <), only p2024 is read. In HASH, BETWEEN 5 AND 6 reads only p1 and p2. After a 2026 partition is added with ADD PARTITION, the same INSERT succeeds. DROP PARTITION p2023 removes the 121 rows of that partition. When the primary key is (id, ordered_at), two rows with the same id and different dates can be inserted. Which partitions are read is decided by the condition and has nothing to do with the number of rows or statistics, but whether an index is used inside a partition and the estimated number of rows depend on the data, so I did not treat them as results. The restriction on the partitioning key and the primary key and the form of conditions that allow pruning also agree with the explanations in the MySQL 8.4 Reference Manual, 26.6.1 Partitioning Keys, Primary Keys, and Unique Keys and 26.4 Partition Pruning. That each partition has its own indexes was confirmed by seeing that the partition set is stored in InnoDB as separate tables and data files, and that a separate PRIMARY index is listed for each partition in the index statistics (mysql.innodb_index_stats). LIST and KEY partitioning, subpartitions, and the cost when there are a very large number of partitions are not covered. Other database products sometimes allow a unique index without the partitioning key, so this restriction is MySQL-specific.

Previous lab: Sharding · Next lab: Redis single-threaded execution

Concepts in this lab

What this lab shows

Settings

Code


      
    


    

      
Diagram

      

      

    


    

      

      

      

    


  


  

    

      
Run comparison

      

      

    

    

      
Try these