Lesson 35 of the SQL Optimization Course: Optimizing MySQL Partitioned Tables for Time Series Queries

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.

This article builds on our previous guide to MySQL time-based partitioning. It focuses on critical SQL best practices for maximizing efficiency in partitioned table queries. While partitioning reduces scanned records, improper queries can negate its benefits.

Key SQL Patterns and Optimization Insights

Consider two tables:

ytt_pt1 (partitioned by log_date)

• ytt_pt1_month1 (partitioned by month)
• While both store similar data, their optimal query structures differ:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Table ytt_pt1 (DATE-based partitioning)
SELECT COUNT(*) 
FROM ytt_pt1 
WHERE log_date BETWEEN '2018-01-01' AND '2019-01-01';

-- Table ytt_pt1_month1 (MONTH-based partitioning)
SELECT COUNT(*) 
FROM ytt_pt1_month1 
WHERE log_date IN (
  '2020-01-01', '2020-01-02', ..., '2020-01-31'
);

The second query uses an explicit IN list due to MySQL’s ​partition pruning​ limitations. Partition pruning (which skips irrelevant partitions) supports functions like TO_DAYS() but not MONTH().

Partition Pruning Mechanics

Verify pruning behavior with a test table pt_pruning (partitioned by TO_DAYS(log_date)):

1
2
3
4
5
6

CREATE TABLE pt_pruning (...) 
PARTITION BY RANGE (TO_DAYS(log_date)) (
  PARTITION p_01 VALUES LESS THAN (TO_DAYS('2020-02-01')),
  ...
  PARTITION p_max VALUES LESS THAN MAXVALUE
);

Queries leveraging pruning (e.g., log_date = ‘2020-01-02’) execute in ​0.03s, while non-prunable ranges (e.g., log_date < ‘2020-02-01’) scan all partitions.

Strategies for Non-Prunable Partitions

If your partition function lacks pruning support:

1. ​Rewrite Queries

Replace ranges with exact values or IN lists. Example:

1
2

-- Optimize range filter to exact match
SELECT COUNT(*) FROM pt_month WHERE log_date = '2020-01-02';

2. ​Use Partition Hints

Direct the optimizer to target specific partitions:

1

SELECT COUNT(*) FROM pt_month PARTITION (p_01) WHERE log_date < '2020-02-01';

Summary

For optimal time-series partitioned tables:

• Prioritize pruning-friendly functions like TO_DAYS().
• Use equality checks or IN lists over complex ranges.
• Apply partition hints when restructuring queries isn’t viable.

Aligning SQL logic with partitioning schemes ensures maximum performance for large datasets.

👋 See you in the next lesson.

Recommended reading

• SQL Optimization Course
• SQL optimization knowledge
• Database industry knowledge

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.

How to use SQLFlash in a database?

• SQLFlash + MySQL
• SQLFlash + PostgreSQL