partition based upon timestamp column per day

[santhosh edukulla]

Hi All,

Currently, we are creating a table test with the following structure as below:

CREATE TABLE test
(
id bigint(11) unsigned,
creationdate Date
PRIMARY KEY(id,creationdate)
)
PARTITION BY RANGE(DAY(creationdate))
(
PARTITION p0 VALUES LESS THAN (2),
..........................
.........
);
etc


So basically, we are partitioning the everydays's data. But, i believe this is resource consuming in terms of insertion and space.(date takes more space compared to time_stamp)

I learnt that I can use "time_stamp" datatype for "creationdate" column and use unix_timestamp function to partition by "day"

Any ideas as how to do that?

Note: The reason for using id,creationdate as primary key is the fact that mysql is not allowing to partition on any non key column here creationdate. So, we added creationdate to key.

This table is huge and gets millions of records per day.

Any help is appreciated.


Thanks!
Santhosh