Partitioning by year and day

[Adam Israel]

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