Advanced MySQL Time-Based Partitioning: SQL Writing Tips and Optimization

This article extends the previous discussion on MySQL time‑based partition implementation, focusing on the SQL writing considerations and optimization techniques for such partitions.

When querying partitioned tables, using the partition key in the WHERE clause generally yields faster results, while queries without the key may scan many partitions and degrade performance.

MySQL provides a partition‑pruning optimization that evaluates the partition function in the query predicate to limit scanned partitions. Only a subset of functions (to_days, to_seconds, year, unix_timestamp) are supported for time‑type columns; the month() function is not.

create table pt_pruning ( id int, r1 int, r2 int, log_date date ) partition by range(to_days(log_date)) ( PARTITION p_01 VALUES LESS THAN (to_days('2020-02-01')) ENGINE = InnoDB, PARTITION p_02 VALUES LESS THAN (to_days('2020-03-01')) ENGINE = InnoDB, ... PARTITION p_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB );

Running three sample queries on pt_pruning shows that queries using the supported partition function execute quickly (≈0.04 s) and benefit from pruning, while a range query using BETWEEN is slightly slower (≈0.06 s) but still acceptable without indexes.

If a table was created with an unsupported partition function (e.g., month()), two optimization paths exist: manually rewrite the SQL to match pruning rules, or add a hint to force partition selection.

(localhost:ytt) select count(*) from pt_month where log_date <= '2020-01-02';

The above query on a table partitioned by month scans all partitions and takes about 1.26 s. Changing the predicate to an equality filter ( log_date = '2020-01-02' ) reduces the scan to a single partition and drops execution time to 0.03 s, demonstrating effective pruning.

Further, converting a range predicate to an IN list ( log_date in ('2020-01-01', ... , '2020-01-31') ) improves performance dramatically (≈0.04 s) because the optimizer can target only the relevant partition. The same effect can be achieved with a partition hint, e.g., select count(*) from pt_month partition (p_01) where log_date < '2020-02-01'; .

In summary, for tables that cannot use MySQL's built‑in partition pruning, follow these rules: use equality or IN list predicates on the partition key, and the same approach works for multi‑table JOINs.