MySQL Forums
Forum List  »  Partitioning

Year and month partitioning
Posted by: Vilhelm Persson
Date: March 02, 2011 02:18AM

Hi
I have data that I want to do year and month partitioning on.
It works fine if I use PARTITION BY RANGE( TO_DAYS(dataTime)
and then type in all month as
PARTITION p_2010_01 VALUES LESS THAN (TO_DAYS('2010-01-01')) ....

But this structure is quite hard to extend when new years are added if the system is ran for many years.

My idea of is to use YEAR as PARTITION and MONTH as SUBPARTITION but I dont get it to work as expected (The explain partitions uses all subpartitions even if I just ask for one week of data)

Example of my code
CREATE TABLE tbtestpart (
userId int(10) unsigned NOT NULL,
dataTime datetime NOT NULL,
dataValue varchar(45) NULL,
PRIMARY KEY (userId,dataTime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE( YEAR(dataTime) )
SUBPARTITION BY HASH( MONTH(dataTime) ) SUBPARTITIONS 12
(
PARTITION p_2010 VALUES LESS THAN (2010),
PARTITION p_2011 VALUES LESS THAN (2011),
PARTITION p_2012 VALUES LESS THAN (2012),
PARTITION p_max VALUES LESS THAN MAXVALUE
);

And then when I add a new year I do
ALTER TABLE tbtestpart REORGANIZE PARTITION p_max INTO (PARTITION p_2013 VALUES LESS THAN (2013),PARTITION p_max VALUES LESS THAN MAXVALUE);

Is this possible to get to work?
There is no demand to use month but around 30 days of data in each partition I think will be good for performance.

Thanks for any help
Vilhelm Persson

Options: ReplyQuote


Subject
Views
Written By
Posted
Year and month partitioning
8491
March 02, 2011 02:18AM
2932
March 02, 2011 03:13AM


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.