Home/Learn/MySQL/Table Partitioning

Table Partitioning

Advanced

Schema Design

RANGE, LIST, HASH, and KEY partitioning split large tables into smaller physical segments; partition pruning lets the optimiser scan only relevant partitions for filtered queries.

Overview

MySQL table partitioning physically splits a single logical table into multiple segments stored separately on disk. The partitioning key determines which partition holds each row. Partition pruning allows the query optimiser to skip irrelevant partitions entirely, dramatically improving performance on large tables when queries filter on the partition key. The four main types are RANGE (continuous value ranges), LIST (discrete value lists), HASH (even distribution via modulo), and KEY (similar to HASH but MySQL-managed). Partitioning is most effective for time-series or archival data; it is not a replacement for proper indexing.

RANGE Partitioning

RANGE partitions divide rows into ranges of the partition key. Ideal for time-series data: each partition holds one month or year of data, and old partitions can be dropped instantly (DROP PARTITION) instead of expensive DELETE operations.

SQL — RANGE partitioning on datetime

-- Partition orders by year of created_at
CREATE TABLE orders (
    id         BIGINT       NOT NULL AUTO_INCREMENT,
    customer_id BIGINT      NOT NULL,
    total      DECIMAL(10,2),
    created_at DATETIME     NOT NULL,
    PRIMARY KEY (id, created_at)   -- partition key must be in PK
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Add a new partition for 2025 (reorganise p_future)
ALTER TABLE orders
    REORGANIZE PARTITION p_future INTO (
        PARTITION p2025 VALUES LESS THAN (2026),
        PARTITION p_future VALUES LESS THAN MAXVALUE
    );

-- Drop 2022 data instantly — no row-level DELETE
ALTER TABLE orders DROP PARTITION p2022;

-- Verify pruning
EXPLAIN SELECT * FROM orders WHERE created_at >= '2024-01-01';
-- partitions column shows only p2024, p_future

LIST & HASH Partitioning

LIST partitions rows by discrete values (e.g. region codes). HASH distributes rows evenly across N partitions using modulo — good for uniform load, but pruning only works when the hash expression appears in the WHERE clause.

SQL — LIST and HASH partitioning

-- LIST partitioning by region
CREATE TABLE sales (
    id      BIGINT NOT NULL,
    region  VARCHAR(20) NOT NULL,
    amount  DECIMAL(10,2),
    PRIMARY KEY (id, region)
) PARTITION BY LIST COLUMNS (region) (
    PARTITION p_emea   VALUES IN ('UK','DE','FR','NL'),
    PARTITION p_apac   VALUES IN ('IN','SG','AU','JP'),
    PARTITION p_amer   VALUES IN ('US','CA','BR','MX')
);

-- HASH partitioning — even distribution, 8 buckets
CREATE TABLE events (
    id         BIGINT NOT NULL,
    user_id    BIGINT NOT NULL,
    event_type VARCHAR(50),
    created_at DATETIME,
    PRIMARY KEY (id, user_id)
) PARTITION BY HASH (user_id) PARTITIONS 8;

-- Pruning with HASH — only works when user_id = constant
EXPLAIN SELECT * FROM events WHERE user_id = 42;
-- ↑ only 1 of 8 partitions scanned

Partition Maintenance & Gotchas

Partitioning adds operational overhead. Foreign keys are not supported on partitioned tables. The partition key must be part of every unique/primary key. Global indexes are not supported — each partition has its own local index.

SQL — partition inspection and maintenance

-- Check partition row counts and sizes
SELECT
    partition_name,
    table_rows,
    ROUND(data_length / 1024 / 1024, 1) AS data_mb
FROM information_schema.partitions
WHERE table_schema = 'shop'
  AND table_name   = 'orders'
ORDER BY partition_name;

-- Rebuild a specific partition (defragment)
ALTER TABLE orders REBUILD PARTITION p2023;

-- Analyse (update statistics for partition)
ALTER TABLE orders ANALYSE PARTITION p2024;

-- Common gotchas:
-- ✗ Foreign keys not allowed on partitioned tables
-- ✗ Partition key must be in every unique/primary key
-- ✗ Subqueries in partition expressions not allowed
-- ✓ Use PARTITION BY RANGE COLUMNS for date/string columns (no YEAR() needed)

Key Points to Remember

1RANGE is best for time-series data; old partitions can be DROPped instantly.
2LIST partitions by discrete values (enums, region codes).
3HASH/KEY spread rows evenly — pruning only applies when the hash key is equality-filtered.
4The partition key must be part of every UNIQUE and PRIMARY key.
5Foreign keys are not supported on partitioned tables.
6Partition pruning is only guaranteed when the WHERE clause filters on the partition expression.

Interview Questions

What is partition pruning and why is it important?

MediumAmazon

When would you choose RANGE partitioning over HASH partitioning?

MediumFlipkart

Why must the partition key be part of the primary key in MySQL?

HardBooking.com

How do you archive old data efficiently using RANGE partitioning?

MediumLinkedIn

What are the limitations of MySQL table partitioning compared to horizontal sharding?

HardUber

Ask Aria about Table Partitioning

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Denormalisation

JSON Data Type & Functions