✏️ Explanatory Question

What is partitioning in MySQL and what are its types?

👁 6 Views
📘 Detailed Answer
🟢 Easy
← Previous What is database replication in MySQL and what are its types?
💡

Answer with Explanation

78

What is partitioning in MySQL and what are its types?

Level: Expert — A powerful technique for managing and querying very large tables efficiently.

Partitioning is the process of dividing a single large table into smaller, more manageable pieces called partitions — while it still appears as one logical table to queries. Each partition is stored and can be managed separately.

The main benefit — "partition pruning": When a query filters on the partition key, MySQL scans only the relevant partitions instead of the whole table, dramatically improving performance on huge datasets.

The Four Partitioning Types

  • RANGE: Partitions based on a range of values (e.g., years, dates).
  • LIST: Partitions based on a discrete list of values (e.g., region codes).
  • HASH: Distributes rows evenly using a hashing function on a column.
  • KEY: Similar to HASH, but uses MySQL's internal hashing (works on any column type, including non-integers).

Partition Types Compared

Type Based On Best For
RANGE Value ranges Time-series (by year/month)
LIST Discrete value list Categorical (region, status)
HASH Hash of a column Even data distribution
KEY Internal hash function Even distribution, any column type

Benefits of Partitioning

  • Faster queries via partition pruning.
  • Easy maintenance — drop an old partition instead of a huge DELETE.
  • Better manageability of very large tables.

Quick Example

-- RANGE partitioning by year
CREATE TABLE sales (
    id         INT,
    sale_date  DATE,
    amount     DECIMAL(10,2)
)
PARTITION BY RANGE ( YEAR(sale_date) ) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);

-- Query hits ONLY the p2025 partition (partition pruning)
SELECT * FROM sales
WHERE sale_date BETWEEN '2025-01-01' AND '2025-12-31';

-- LIST partitioning by region
CREATE TABLE customers (
    id     INT,
    region VARCHAR(10)
)
PARTITION BY LIST COLUMNS(region) (
    PARTITION p_east VALUES IN ('WB','BR','OD'),
    PARTITION p_west VALUES IN ('MH','GJ','RJ')
);

-- HASH partitioning into 4 buckets
CREATE TABLE logs (
    id    INT,
    msg   TEXT
)
PARTITION BY HASH(id) PARTITIONS 4;

-- Fast maintenance: drop an entire old partition instantly
ALTER TABLE sales DROP PARTITION p2023;
Key limitation: The partition column must be part of every unique key (including the primary key). This is a common constraint that trips people up when designing partitioned tables.
Interviewer tip: The one-liner they want — "Partitioning splits a large table into smaller partitions that act as one logical table. The four types are RANGE, LIST, HASH, and KEY, and the main benefit is partition pruning for faster queries."
← Previous What is database replication in MySQL and what are its types?