Re: make number of partitions variable

[Phil Hildebrand]

Is there any reason _dateTime can't be stored as a datetime ?

I don't believe you can create a table such that it will auto create partitions for you if that's what you are trying to do.

You should probably just pre-create the partitions, and then merge/split them when necessary. (probably makes sense to use a range partition here...)

Something like:

alter table herearetheticks
partition by range (to_days ( _dateTime ))
(
partition "p_2008-05-30" values less than (733557),
partition "p_2008-05-31" values less than (733558),
partition "p_2008-06-01" values less than (733559),
partition p_max values less than MAXVALUE
)

Note: _dateTime must be part of the primary key for partitions...

If _dateTime is a true datetime, then the optimizer will prune the partitions correctly:

explain partitions select * from herearetheticks where _dateTime < now();
+----+-------------+-----------------+---------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+---------------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | herearetheticks | p_2008-05-30,p_2008-05-31 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-----------------+---------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Then just add partitions as necessary vi re-organize:

alter table herearetheticks reorganize partition p_max into
( partition "p_2008-06-11" values less than (733569),
partition p_max values less than maxvalue
)

It's fairly straight forward to write a store procedure/script to calculate the next 30 days of partitions:

DELIMITER //

CREATE PROCEDURE get_parts()
BEGIN
DECLARE counter INT DEFAULT 30;
DECLARE partno INT DEFAULT 1;

SET partno = TO_DAYS(NOW());

CREATE TEMPORARY TABLE my_partitions
(
id INT AUTO_INCREMENT PRIMARY KEY,
partition_info VARCHAR(256)
);

WHILE counter > 0 DO
INSERT INTO my_partitions (partition_info)
SELECT CONCAT( 'partition "P_', FROM_DAYS(partno), '" VALUES LESS THAN (',partno + 1,'),' );
SET counter = counter - 1;
SET partno = partno + 1;
END WHILE;

SELECT partition_info FROM my_partitions ORDER BY id ASC;

END;
//

DELIMITER ;