List Partitioning in MySQL: What It Is and How It Works

In MySQL, list partitioning is a method to divide a table into parts based on a list of discrete values in a column. Each partition holds rows matching specific values, making data management and queries faster for those values.

How It Works

List partitioning in MySQL works by splitting a table into smaller parts called partitions. Each partition contains rows where the partitioning column matches one or more specific values from a list. Think of it like sorting mail into different bins based on zip codes: each bin holds mail for certain zip codes only.

This helps MySQL quickly find and manage data because it only looks in the relevant partition instead of scanning the whole table. For example, if you partition a sales table by region codes, queries for a specific region only check that region's partition, saving time.

Example

This example shows how to create a table partitioned by a list of values in the region column. Each partition holds rows for specific regions.

sql

CREATE TABLE sales (
  id INT,
  product VARCHAR(50),
  region VARCHAR(10),
  amount DECIMAL(10,2)
)
PARTITION BY LIST COLUMNS(region) (
  PARTITION p_north VALUES IN ('North', 'N'),
  PARTITION p_south VALUES IN ('South', 'S'),
  PARTITION p_east VALUES IN ('East', 'E'),
  PARTITION p_west VALUES IN ('West', 'W')
);

Output

Query OK, 0 rows affected (0.05 sec)

When to Use

Use list partitioning when your data naturally groups into distinct categories or values, like regions, departments, or product types. It is helpful when queries often filter by these categories, improving speed by scanning fewer rows.

For example, a company with sales data divided by region can use list partitioning to quickly access sales for a specific region without scanning the entire table. It also helps with maintenance tasks like archiving or deleting data for certain categories.

Key Points

• List partitioning divides a table based on specific column values.
• Each partition holds rows matching a list of values.
• Improves query performance by limiting data scanned.
• Useful for categorical data like regions or departments.
• Supports easier data management and maintenance.

Key Takeaways

List partitioning splits a table into parts based on specific column values.

It speeds up queries by scanning only relevant partitions.

Ideal for data grouped by categories like regions or types.

Simplifies data management and maintenance tasks.

Partitions are defined by listing values for each group.