Perhaps someone has already accomplished this:
I have a simple table with 3 columns:
myid BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
myunixtime INT(11) NOT NULL DEFAULT 0,
myvalue BIGINT(20) UNSIGNED NOT NULL DEFAULT 0
It is collecting millions of rows.
The myunixtime column is a unix timestamp column.
I'd love to know if it is possible to partition the table so that the
partitions would be something like:
partition A = everything one day or less old,
partition B = everything 7 days old or less,
partition C = everything 31 days old or less,
partition D = everything older than 31 days.
Can partitioning be this dynamic? If not, what solution could be
suggested to handle doing date range queries on this table that can have
10's or 100's of millions of rows?