LIST Partitioning

What is LIST Partitioning?

LIST partitioning is used when you want to divide data based on a predefined set of discrete categorical values. Unlike RANGE partitioning (which groups data by numerical or time intervals), LIST partitioning explicitly lists the exact values that should be stored in each partition.

This is especially useful when your data naturally falls into fixed groups—such as departments, product categories, regions, or statuses. Because you specify the exact values, MySQL knows exactly which partition a row belongs to, allowing faster queries and simpler maintenance.

LIST partitioning also helps keep related categories together and provides flexibility when expanding or reorganizing category groups.

Implementing LIST Partitioning in MySQL

CREATE TABLE student_departments (
   student_id INT,
   student_name VARCHAR(50),
   department VARCHAR(50)
)
PARTITION BY LIST COLUMNS (department) (
   PARTITION p_cs VALUES IN ('Computer Science', 'IT'),
   PARTITION p_eng VALUES IN ('Mechanical', 'Electrical'),
   PARTITION p_biz VALUES IN ('Business', 'Economics')
);

How this works:

Students from Computer Science or IT go into the p_cs partition
Students from Mechanical or Electrical engineering go into p_eng
Students from Business or Economics go into p_biz

If a row contains a department not listed in any partition, MySQL will throw an error unless you’ve defined a catch-all partition.

Use Cases for LIST Partitioning

LIST partitioning works best when you have stable, well-defined categories with no natural numeric ordering. Common scenarios include:

1. Region-based or Location-based Data
States, cities, zones
Shipping regions (e.g., North, South, East, West)
Useful for logistics, sales, or region-wise reporting.

2. Departmental or Category Segmentation
Academic departments
Product categories
Employee teams or business units
Makes it easy to query and maintain department-specific data.

3. Non-numeric but Well-defined Groups
Since LIST supports strings, it is ideal when:
The categories are not numeric
They do not follow a range
They are limited and known in advance
For example: status codes ('active', 'inactive', 'pending'), membership types, or predefined labels.