Intermediate LevelSQL & MySQL
What is Database Table Partitioning (Range, List, Hash) and Partition Pruning?
❓ Interview Prompt: Explain Table Partitioning, Range Partitioning, List Partitioning, Hash Partitioning, and Partition Pruning execution.
Table Partitioning splits a large database table into smaller physical file segments on disk while presenting a single unified table view to queries:
3 Partitioning Strategies:
1
Range Partitioning: Maps rows to partitions based on value ranges (e.g. 'PARTITION BY RANGE (YEAR(sale_date))').
2
List Partitioning: Maps rows based on discrete value lists (e.g. 'PARTITION BY LIST (region)').
3
Hash Partitioning: Distributes rows evenly using an internal hash function.
•
Partition Pruning: The query optimizer skips reading non-matching partition files entirely during execution!
Code Implementation Example
sql
CREATE TABLE sales (
id BIGINT AUTO_INCREMENT,
sale_date DATE NOT NULL,
amount DECIMAL(10, 2),
PRIMARY KEY (id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p2026 VALUES LESS THAN (2027)
);💡Range partitioning a sales history table by year.
🎯 Interview Key Takeaway
Partition large tables to enable Partition Pruning and accelerate historical data maintenance.
#SQL#Partitioning#Partition Pruning#Range Partition#Intermediate
Back to all SQL & MySQL Questions→