Table Partitioning Principles: Range, List, Hash, Key0%

Table Partitioning Principles: Range, List, Hash, Key

Advanced14 min read•Updated: 2026-09-12

Table Partitioning Principles: Range, List, Hash, Key

When a single table grows beyond tens or hundreds of millions of rows, maintaining B+Tree index depths and performing table maintenance becomes increasingly painful.

Table Partitioning allows a single logical table to be physically split into smaller, independent chunks (partitions) across storage, governed by a Partitioning Key.

The Fundamental Rule of Partitioning in MySQL

THE PRIMARY KEY MANDATE: In MySQL, every unique key on the table (including the PRIMARY KEY) MUST contain every column in the table's partitioning expression! You cannot partition by order_date if your primary key is simply order_id; the primary key must be composite: PRIMARY KEY (order_id, order_date).

The Four Core Partitioning Types

1. RANGE Partitioning

Assigns rows to partitions based on whether the partitioning key value falls within a specified discrete range. Ideal for date-based data archiving:

1CREATE TABLE orders_range (
2    order_id INT NOT NULL,
3    order_date DATE NOT NULL,
4    total_amount DECIMAL(10, 2),
5    PRIMARY KEY (order_id, order_date)
6) ENGINE = InnoDB
7PARTITION BY RANGE (YEAR(order_date)) (
8    PARTITION p2023 VALUES LESS THAN (2024),
9    PARTITION p2024 VALUES LESS THAN (2025),
10    PARTITION p2025 VALUES LESS THAN (2026),
11    PARTITION p_future VALUES LESS THAN MAXVALUE
12);

2. LIST Partitioning

Assigns rows based on matching one of a set of discrete enumerated values. Ideal for geographic segmentation:

1CREATE TABLE customers_list (
2    customer_id INT NOT NULL,
3    country_code VARCHAR(2) NOT NULL,
4    PRIMARY KEY (customer_id, country_code)
5) ENGINE = InnoDB
6PARTITION BY LIST COLUMNS (country_code) (
7    PARTITION p_americas VALUES IN ('US', 'CA', 'MX'),
8    PARTITION p_europe VALUES IN ('UK', 'DE', 'FR'),
9    PARTITION p_asia VALUES IN ('IN', 'JP', 'SG')
10);

3. HASH Partitioning

Distributes rows evenly across a predetermined number of partitions using a user-defined expression:

1CREATE TABLE web_events (
2    event_id INT NOT NULL,
3    user_id INT NOT NULL,
4    created_at DATETIME NOT NULL,
5    PRIMARY KEY (event_id, user_id)
6) ENGINE = InnoDB
7PARTITION BY HASH (user_id)
8PARTITIONS 8;

4. KEY Partitioning

Similar to HASH, but MySQL uses its internal MD5-based hashing algorithm on the columns:

1CREATE TABLE user_tokens (
2    token_uuid VARCHAR(36) PRIMARY KEY
3) ENGINE = InnoDB
4PARTITION BY KEY ()
5PARTITIONS 16;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the fundamental requirement regarding Primary Keys when partitioning a table in MySQL?

Question 2

Which partitioning type is most suitable for archiving time-series logs by year or month?

Question 3

What does VALUES LESS THAN MAXVALUE accomplish in RANGE partitioning?

Question 4

Which partitioning type distributes rows evenly using an internal MySQL hashing algorithm across a set number of buckets?

Question 5

If a table is partitioned into 4 partitions, how does the application query it?

Next Lesson

Creating & Managing Partitioned Tables

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Advanced Analytical JSON Queries	Creating & Managing Partitioned Tables

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Creating & Managing Partitioned Tables

Table Partitioning Principles: Range, List, Hash, Key

Advanced14 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Table Partitioning Principles: Range, List, Hash, Key

When a single table grows beyond tens or hundreds of millions of rows, maintaining B+Tree index depths and performing table maintenance becomes increasingly painful.

Table Partitioning allows a single logical table to be physically split into smaller, independent chunks (partitions) across storage, governed by a Partitioning Key.

The Fundamental Rule of Partitioning in MySQL

The Four Core Partitioning Types

1. RANGE Partitioning

Assigns rows to partitions based on whether the partitioning key value falls within a specified discrete range. Ideal for date-based data archiving:

1CREATE TABLE orders_range (
2    order_id INT NOT NULL,
3    order_date DATE NOT NULL,
4    total_amount DECIMAL(10, 2),
5    PRIMARY KEY (order_id, order_date)
6) ENGINE = InnoDB
7PARTITION BY RANGE (YEAR(order_date)) (
8    PARTITION p2023 VALUES LESS THAN (2024),
9    PARTITION p2024 VALUES LESS THAN (2025),
10    PARTITION p2025 VALUES LESS THAN (2026),
11    PARTITION p_future VALUES LESS THAN MAXVALUE
12);

2. LIST Partitioning

Assigns rows based on matching one of a set of discrete enumerated values. Ideal for geographic segmentation:

1CREATE TABLE customers_list (
2    customer_id INT NOT NULL,
3    country_code VARCHAR(2) NOT NULL,
4    PRIMARY KEY (customer_id, country_code)
5) ENGINE = InnoDB
6PARTITION BY LIST COLUMNS (country_code) (
7    PARTITION p_americas VALUES IN ('US', 'CA', 'MX'),
8    PARTITION p_europe VALUES IN ('UK', 'DE', 'FR'),
9    PARTITION p_asia VALUES IN ('IN', 'JP', 'SG')
10);

3. HASH Partitioning

Distributes rows evenly across a predetermined number of partitions using a user-defined expression:

1CREATE TABLE web_events (
2    event_id INT NOT NULL,
3    user_id INT NOT NULL,
4    created_at DATETIME NOT NULL,
5    PRIMARY KEY (event_id, user_id)
6) ENGINE = InnoDB
7PARTITION BY HASH (user_id)
8PARTITIONS 8;

4. KEY Partitioning

Similar to HASH, but MySQL uses its internal MD5-based hashing algorithm on the columns:

1CREATE TABLE user_tokens (
2    token_uuid VARCHAR(36) PRIMARY KEY
3) ENGINE = InnoDB
4PARTITION BY KEY ()
5PARTITIONS 16;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the fundamental requirement regarding Primary Keys when partitioning a table in MySQL?

Question 2

Which partitioning type is most suitable for archiving time-series logs by year or month?

Question 3

What does VALUES LESS THAN MAXVALUE accomplish in RANGE partitioning?

Question 4

Which partitioning type distributes rows evenly using an internal MySQL hashing algorithm across a set number of buckets?

Question 5

If a table is partitioned into 4 partitions, how does the application query it?

Next Lesson

Creating & Managing Partitioned Tables

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Advanced Analytical JSON Queries	Creating & Managing Partitioned Tables

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next