Partitioning in MySQL
Fast recall
Quick revision
Core answer
Tests knowledge of advanced MySQL features for managing large tables.
Interviewers want to know whether the candidate understands partition pruning, its limitations with foreign keys, and whether they would reach for partitioning vs.
sharding vs. archiving for a given scale problem.
Interview-ready guide
Interview answer
MySQL partitioning splits a large table into smaller physical segments while presenting them as one logical table. It's most effective for time-series data where queries are naturally bounded by a date range.
How partitioning helps
- When a query includes the partition key in the WHERE clause, MySQL's query optimizer performs partition pruning — it reads only the relevant partitions, which means a query for last month's data scans one partition instead of scanning the entire 10-billion-row table
- This also makes archiving old data fast: dropping a range partition (
ALTER TABLE t DROP PARTITION p2022) removes an entire month of data in milliseconds, much faster thanDELETE WHERE created_at < '2023-01-01'which generates binlog events for every row
Partition types
- RANGE partitioning on a date or integer column is the most common —
PARTITION BY RANGE (YEAR(created_at))creates separate segments per year, and partition pruning eliminates years the query doesn't need - LIST partitioning works on discrete values —
PARTITION BY LIST (region_id)— useful for multi-tenant data where queries are always scoped to one region - HASH and KEY partitioning distribute rows evenly by hash — useful for write distribution across multiple tablespaces, less useful for query pruning
What partitioning doesn't fix
- Queries that don't include the partition key in WHERE have no pruning — they scan all partitions, which is often slower than an equivalent full table scan due to per-partition overhead
- Foreign keys are not supported on partitioned tables in MySQL — a significant limitation for schemas with referential integrity
Consider partitioning when a table has hundreds of millions of rows, queries are consistently range-bounded by a high-cardinality date or integer column, and archiving old data is a regular operation.
Partitioning in MySQL
Direct Answer
MySQL partitioning splits a large table into smaller physical sub-tables while presenting a single logical table to the application. The four main types are RANGE (split by value range, e.g., date), LIST (split by explicit values), HASH (even distribution), and KEY (similar to HASH using MySQL's own hash). Partitioning helps prune scans on large time-series or event tables but is not a substitute for proper indexing.
Code Example
MySQL RANGE partitioning on a date column — events table
-- RANGE partitioning: rows assigned to partitions by year
CREATE TABLE events (
id BIGINT NOT NULL AUTO_INCREMENT,
user_id INT NOT NULL,
event_type VARCHAR(50),
created_at DATETIME NOT NULL,
payload JSON,
PRIMARY KEY (id, created_at) -- partition key MUST be in PK for InnoDB
)
PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
-- Partition pruning in action:
-- This query only scans p2024, skipping all other partitions
SELECT * FROM events WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
-- Verify pruning with EXPLAIN PARTITIONS
EXPLAIN SELECT * FROM events WHERE created_at >= '2024-06-01';
-- partitions column shows only: p2024,p2025,p_future (not p2022, p2023)
-- Drop old data efficiently: just drop the partition (instant DDL)
ALTER TABLE events DROP PARTITION p2022;
-- vs: DELETE FROM events WHERE YEAR(created_at) = 2022 (slow, locks table)
-- Add a new partition before p_future fills up:
ALTER TABLE events REORGANIZE PARTITION p_future INTO (
PARTITION p2026 VALUES LESS THAN (2027),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
-- LIMITATION: InnoDB partitioned tables CANNOT have foreign keys
-- Spring Boot JPA: use @CreationTimestamp on created_at to ensure it's always set
Common Mistakes & Pitfalls
Interviewers want to know whether the candidate understands partition pruning, its limitations with foreign keys, and whether they would reach for partitioning vs.
Check your understanding
Answer these without looking back at the article.
- How does partition pruning work and what prevents it from activating?
- What are the alternatives to partitioning for managing large tables in MySQL — archiving, sharding, Vitess?
- How would you add partitioning to an existing large production table without downtime?
Related interview prep categories
2Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.