Time-Based Partitioning Strategies in MySQL: Year, Month, and Day Granularity

When partitioning large tables, time fields are the most common basis; this article describes how to partition MySQL tables by year, month, and day, providing concrete SQL statements, stored procedures, and performance observations for each approach.

Partition by Year

Selecting a yearly granularity is suitable when queries filter by a specific year. Example query:

select * from ytt_pt1 where log_date >='2018-01-01' and log_date < '2019-01-01';

Creating a table and adding yearly range partitions:

mysql> create table ytt_pt1(id bigint, log_date date);
mysql> insert into ytt_pt1 select id,log_date from ytt_p1 limit 10000000;
mysql> ALTER TABLE ytt_pt1 PARTITION BY RANGE (year(log_date)) (
    PARTITION p0001 VALUES LESS THAN (2012),
    PARTITION p0002 VALUES LESS THAN (2013),
    ...
    PARTITION p0010 VALUES LESS THAN (2021),
    PARTITION p_max VALUES LESS THAN (maxvalue)
);

Querying a specific year uses the corresponding partition (e.g., p0008) and runs in about 0.91 seconds. However, using expressions like year(log_date) = '2018' forces a full‑partition scan, taking over 9 seconds.

To force a specific partition, you can use the PARTITION(p0008) hint or create a virtual column.

Partition by Month

Two methods are shown:

List partitioning on the month(log_date) function, creating 12 partitions (one per month).

Range‑by‑columns partitioning that creates a separate partition for each month of each year, resulting in 144 partitions for a 12‑year span.

Example of the first method:

CREATE TABLE `ytt_pt1_month1` (
  `id` bigint DEFAULT NULL,
  `log_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY LIST (month(`log_date`))
(PARTITION p0001 VALUES IN (1) ENGINE = InnoDB,
 PARTITION p0002 VALUES IN (2) ENGINE = InnoDB,
 ...
 PARTITION p0012 VALUES IN (12) ENGINE = InnoDB) */;

Querying the first half of January 2020 on this table scans partition p0001 and takes about 0.66 seconds.

The second method uses a stored procedure to generate partitions for each year‑month combination:

DELIMITER $$
USE `ytt`$$
DROP PROCEDURE IF EXISTS `sp_add_partition_ytt_pt1_month2`$$
CREATE PROCEDURE `sp_add_partition_ytt_pt1_month2`()
BEGIN
    DECLARE i INT DEFAULT 2010;
    DECLARE j INT;
    DECLARE v_tmp_date DATE;
    SET @stmt = '';
    SET @stmt_begin = 'ALTER TABLE ytt_pt1_month2 PARTITION BY RANGE COLUMNS (log_date)(';
    WHILE i <= 2020 DO
        SET j = 1;
        WHILE j <= 12 DO
            SET v_tmp_date = CONCAT(i,'-01-01');
            SET @stmt = CONCAT(@stmt,'PARTITION p',i,'_',LPAD(j,2,"0"),' VALUES LESS THAN (''',DATE_ADD(v_tmp_date,INTERVAL j MONTH),'''),');
            SET j = j + 1;
        END WHILE;
        SET i = i + 1;
    END WHILE;
    SET @stmt_end = 'PARTITION p_max VALUES LESS THAN (maxvalue))';
    SET @stmt = CONCAT(@stmt_begin,@stmt,@stmt_end);
    PREPARE s1 FROM @stmt;
    EXECUTE s1;
    DROP PREPARE s1;
END$$
DELIMITER ;
CALL sp_add_partition_ytt_pt1_month2;

This approach reduces the same query time to about 0.06 seconds, roughly ten times faster than the simple 12‑partition design.

Partition by Day

Day‑level partitioning yields the fastest queries for single‑day filters. MySQL allows up to 8192 partitions, so a 10‑year range (≈3650 partitions) is acceptable. A stored procedure creates daily partitions:

DELIMITER $$
USE `ytt`$$
DROP PROCEDURE IF EXISTS `sp_add_partition_ytt_pt1_day`$$
CREATE PROCEDURE `sp_add_partition_ytt_pt1_day`(
    IN f_year_start YEAR,
    IN f_year_end YEAR
)
BEGIN
    DECLARE i INT DEFAULT f_year_start;
    DECLARE j INT;
    DECLARE v_year DATE;
    DECLARE v_log_date DATE;
    SET @stmt = '';
    SET @stmt_begin = 'ALTER TABLE ytt_pt1_day PARTITION BY RANGE COLUMNS (log_date)(';
    WHILE i <= f_year_end DO
        SET v_year = CONCAT(i,'-01-01');
        SET j = 1;
        WHILE j <= 365 DO
            SET v_log_date = DATE_ADD(v_year,INTERVAL j DAY);
            SET @stmt = CONCAT(@stmt,'PARTITION p',i,'_',LPAD(j,3,'0'),' VALUES LESS THAN (''',v_log_date,'''),');
            SET j = j + 1;
        END WHILE;
        SET i = i + 1;
    END WHILE;
    SET @stmt_end = 'PARTITION p_max VALUES LESS THAN (maxvalue))';
    SET @stmt = CONCAT(@stmt_begin,@stmt,@stmt_end);
    PREPARE s1 FROM @stmt;
    EXECUTE s1;
    DROP PREPARE s1;
END$$
DELIMITER ;
CALL sp_add_partition_ytt_pt1_day('2010','2020');

After partitioning by day, a count query on a single date runs in about 0.01 seconds, far faster than the same query on the year‑ or month‑partitioned tables (0.68 s and 0.87 s respectively).

Conclusion

The article demonstrates the three most common time‑based partitioning methods in MySQL—by year, month, and day—showing how to create the partitions, sample queries, and performance trade‑offs, helping practitioners choose the appropriate granularity for their workload.