Year and month partitioning

[Vilhelm Persson]

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