MySQL Forums
Forum List  »  Partitioning

Re: make number of partitions variable
Posted by: Phil Hildebrand
Date: May 30, 2008 07:58PM

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 ;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: make number of partitions variable
2906
May 30, 2008 07:58PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.