MariaDB Table Partitioning: How to Split Data for Faster Queries

Use‑Case Snapshot

When a table grows to millions of rows, full table scans become expensive. MariaDB’s partitioning lets you split that logical table into smaller physical segments, so the optimizer can skip entire partitions that can’t contain the requested data. The result is faster queries and easier data lifecycle management.

How Partitioning Works – Core Syntax

Partitioning is declared in the CREATE TABLE statement with the PARTITION BY clause. MariaDB supports four partitioning methods: RANGE, LIST, HASH, and KEY. The most common for time‑series data is RANGE on a date or integer column.

CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id, sale_date)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027)
);

Key points:

• Partition key (here YEAR(sale_date)) must be part of the table’s primary key or a unique key.
• Each PARTITION line defines a sub‑table. The VALUES LESS THAN expression is the upper bound; data equal to the bound belongs to the next partition.
• All partitions share the same schema; you cannot add columns to a single partition.

Practical Example – Query Pruning

Assume we insert sample data spanning 2024 and 2025:

INSERT INTO sales (id, sale_date, amount) VALUES
(1, '2024-03-15', 120.00),
(2, '2024-07-22', 75.50),
(3, '2025-01-10', 200.00),
(4, '2025-05-05', 50.25);

Now run a query that targets only 2024:

EXPLAIN SELECT * FROM sales
WHERE sale_date BETWEEN '2024-01-01' AND '2024-12-31';

The optimizer will return a plan that references only p2024. In the SELECT plan you’ll see a line like:

Table: sales
Partitions: p2024

That’s partition pruning: the engine skips p2025 and p2026 entirely, reducing I/O and CPU.

Adding and Dropping Partitions

Partitions can be added or removed without rebuilding the whole table, but the operation still requires a table lock and can be I/O intensive if the table is large.

Adding a New Partition

ALTER TABLE sales ADD PARTITION (
    PARTITION p2027 VALUES LESS THAN (2028)
);

After adding, new rows with sale_date in 2027 will land in p2027.

Dropping a Partition (Caution)

Dropping removes all data in that segment permanently.

ALTER TABLE sales DROP PARTITION p2024;
SELECT COUNT(*) FROM sales;

Verify the count reflects the loss of 2024 rows. Always back up or export the partition before dropping.

Limitations & Common Mistakes

• Predicate Must Reference Partition Key: Partition pruning only works when the WHERE clause uses the partition key in a supported form (e.g., =, <, >, BETWEEN). Using a non‑key column blocks pruning, negating the benefit.
• Adding Partitions Can Trigger a Full Rebuild: If you add a partition that requires data redistribution (e.g., changing the partitioning expression), MariaDB may rebuild the table, locking it for minutes to hours depending on size.
• Too Many Partitions: While MariaDB supports up to 1024 partitions per table, having hundreds can degrade metadata performance and increase maintenance overhead.
• Indexing Inside Partitions: Each partition inherits the table’s indexes. Adding a new index after partitioning requires ALTER TABLE on the whole table, which may lock all partitions.
• Partitioning Is Not a Replacement for Proper Indexes: For queries that filter on non‑partition columns, indexes are still essential. Partitioning should complement, not replace, indexing strategy.
• **Dropping Partitions Is Irreversible**: Once dropped, data is lost unless you have a backup. Use ALTER TABLE ... DROP PARTITION only when you’re sure the data is archived elsewhere.

Best Practices

1. Choose a Stable Partition Key: Prefer a column that never changes, such as a date or integer that monotonically increases.
2. Keep Partitions Manageable: Aim for 5–10 partitions per year for time‑series tables; adjust based on data volume and query patterns.
3. Automate Partition Maintenance: Use ALTER TABLE ... ADD PARTITION via cron or event scheduler to roll over new years.
4. Back Up Before Dropping: Export the partition with SELECT * INTO OUTFILE or use mysqldump --single-transaction --skip-lock-tables.
5. Monitor Partition Usage: Check information_schema.PARTITIONS for size and row counts to identify stale or oversized partitions.
6. Test Query Plans: Run EXPLAIN before and after adding partitions to confirm pruning works as expected.

When to Use Partitioning

• Large tables where most queries target a subset of data, such as logs, sales, or sensor feeds.
• Data retention policies that require quick archival or deletion of old data.
• Workloads that benefit from parallel I/O, as each partition can be read independently by the storage engine.

Final Takeaway

MariaDB table partitioning is a powerful tool for slicing large datasets into manageable pieces. By defining a stable partition key and ensuring queries reference that key, you can let the optimizer prune irrelevant partitions, thus speeding up reads and simplifying data lifecycle management. Remember the limitations—particularly the impact of adding or dropping partitions—and pair partitioning with solid indexing for best performance.