MySQL Forums
Forum List  »  Partitioning

partition based upon timestamp column per day
Posted by: santhosh edukulla
Date: March 01, 2011 03:55AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
partition based upon timestamp column per day
5044
March 01, 2011 03:55AM


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.