This is one cool post.
It’s a shame that MySQL doesnt have the ability to automatically add partitions based upon date. For example, if result of a function should generate a new partition:
PARTITION BY YEARWEEK(MyDateTimeCol)
And hey presto, a bunch of new partitions would appear every time you insert new data thats in a new range. YEARWEEK() may be too course for your massive amount of data – perhaps TO_DAYS(). Perhaps it’s not that massive a data set, so just YEAR() would suffice. But having the new partitions created upon INSERT of data that matches would be great. As would deletion of partitions when a partion is empty. Nice bit of housekeeping.