MySQL Forums
Forum List  »  Partitioning

Partitioning by year and day
Posted by: Adam Israel
Date: June 16, 2011 01:52AM

I'm relatively new to partitioning in MySQL but familiar with the concept. In previous applications, I've named tables by timestamp (records_2009, records_2010, records_2011, etc) and used a stored procedure to dynamically select from the correct table.

I need to partition the data by day, which I've done like this:

CREATE TABLE records_2011 (
id integer AUTO_INCREMENT,
record_date datetime,
...
PRIMARY KEY (id, AUTO_INCREMENT)
) ENGINE=InnoDB;

ALTER TABLE records_2011
PARTITION by range (to_days(record_date))
(
partition p001 VALUES LESS THAN (to_days('2011-01-01'))
, partition p002 VALUES LESS THAN (to_days('2011-01-02'))
, partition p003 VALUES LESS THAN (to_days('2011-01-03'))
...

, partition p365 VALUES LESS THAN (to_days('2011-12-31'))
);

This still leaves me with the problem of having to change the table I'm selecting from. Ideally, I'd like to just have a 'records' table and have that also partitioned by year.

I thought maybe partitions/subpartitions might work, but there's still the limit of 1024 partitions I'd need to purge partitions older than 3 years old, which isn't necessarily bad for my application but I want to make sure there isn't a better way.

Thoughts or recommendations?

Thanks,

Adam Israel

Options: ReplyQuote


Subject
Views
Written By
Posted
Partitioning by year and day
3232
June 16, 2011 01:52AM
1682
June 17, 2011 09:34AM
1680
June 17, 2011 09:48AM


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.