Lesson 34 of the SQL Optimization Course: Partition Pruning Techniques for Time-Based Tables

Introduction

For relational databases, the design of tables and SQL is written are particularly crucial. It wouldn’t be an exaggeration to say that they account for 90% of performance. So this time, specifically targeting these two major knowledge areas, we’ll conduct a detailed analysis for you, peeling back the layers.

This Series uses plain and understandable language and selects a large number of examples to elaborate on the subtleties for you.

🧑‍💻 Target audience:

DBA
Database developers
Students

We will use MySQL as the demonstration database.

Understanding Partition Pruning in MySQL

Partition pruning is a critical optimization technique in MySQL that enhances query performance by reducing the number of scanned partitions. This article explores practical strategies for leveraging partition pruning in time-based partitioned tables.

Common Challenges in Time-Based Partitioning

When working with time-based partitioned tables like ytt_pt1_month1, developers often encounter inefficiencies due to MySQL’s limited support for certain partition functions. For example:

1
2
3
4
-- Suboptimal query structure
SELECT COUNT(*) 
FROM ytt_pt1_month1 
WHERE log_date IN ('2020-01-01', '2020-01-02', ..., '2020-01-31');

This query’s inefficiency stems from MySQL’s inability to prune partitions effectively when using non-supported functions like MONTH().

Advanced Optimization Strategies

1. Embrace Partition Key Equality Checks

Use direct equality checks on partition keys to leverage pruning:

1
2
3
4
-- Optimized query targeting a single partition
SELECT COUNT(*) 
FROM pt_month 
WHERE log_date = '2020-01-02';

This approach reduces scan time to 0.03 seconds by isolating the relevant partition (p_01).

2. Replace Range Queries with Partition-Friendly Lists

Convert range conditions into explicit lists for better pruning:

1
2
3
4
5
6
-- List-based query for January 2020
SELECT COUNT(*) 
FROM pt_month 
WHERE log_date IN (
  '2020-01-01', '2020-01-02', ..., '2020-01-31'
);

This modification executes in 0.04 seconds, compared to 1.93 seconds for the original range query.

3. Leverage MySQL Hints for Explicit Partition Selection

Direct the optimizer to specific partitions using hints:

1
2
3
4
-- Query with partition hint
SELECT COUNT(*) 
FROM pt_month PARTITION (p_01) 
WHERE log_date < '2020-02-01';

This results in 0.04 seconds execution time by bypassing unnecessary partitions.

Best Practices for Partitioned Table Design

Prioritize Supported Partition Functions: Use TO_DAYS(), YEAR(), or UNIX_TIMESTAMP() for optimal pruning.
Avoid Non-Prunable Functions: Functions like MONTH() limit pruning capabilities.
Combine Partitioning with Indexes: Enhance performance further by indexing partition keys.

Summary

By aligning SQL queries with MySQL’s partition pruning mechanics—through equality checks, list-based filtering, and strategic hints—developers can achieve significant performance gains. For legacy systems using non-prunable functions, manual query refactoring or hints offer viable workarounds.

👋 See you in the next lesson.

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.