Partitioning by year and day
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
Subject
Views
Written By
Posted
Partitioning by year and day
4810
June 16, 2011 01:52AM
2147
June 17, 2011 09:34AM
2083
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.