Hi,
This is what Partitioning introduced in 5.1 will do for you.
You can choose either LIST partitioning or RANGE partitioning it sounds to me.
There are new SQL commands to
DROP partitions (called COALESCE for HASH partitions)
ADD partitions
REORGANISE partitions (split or merge or change ranges or list values)
and also
OPTIMIZE partitions is on its way in.
If you have specific requirements that you want to make sure that they are met please
indicate them. There is still a small window of opportunity for the 5.1 development and
naturally as well for the 5.2 development.
There are a couple of posts on this forum that will help you understand what's coming in the
5.1 version on partitioning.
There's already now a source downloadable version of 5.1 with partitioning available. There will
be a number of improvements of various things in future releases and a few syntactical additions
but most of the syntax for handling partitions is already there.
Rgrds Mikael
JinNo Kim wrote:
> I was forwarded here from the Performance list,
> and was wondering if it might be possible to make
> the leap to daily (within month/year partitions)
> at some point.
>
> I'm playing with an application that inserts
> ~10-30 million rows of (multi)integer+(2)datetime
> data fields /day and will attempt to maintain
> months (or years) of storage. I'm well over 2
> billion rows in the daily tables, after recovering
> from the first hiccup in my original
> implementation plan. I am currently messing
> around with daily tables+merges to get in the
> neighborhood of our desired application, but this
> sounds like a very real solution to something I've
> been slaving over in shell scripts.
Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:
http://mikaelronstrom.blogspot.com