I have recently worked on a MySQL database system which had numerous tables running into the billions of rows of data, which was fairly write heavy in usage. A slight advantage I had was the tables only needed to contain about 5 years worth of data - this got me thinking. Could I create a partitioning scheme which allowed the years to wrap nicely at the end of the partition? I also had the advantage that many of the queries performed were on either quarterly or yearly data - be that calendar or financial.
What I can up with was the following partitioning function against the date field - this did require having the date field in the primary key, but that wasn't much of a hardship for the benefits it produced...
PARTITION BY HASH (( YEAR(`Period`) + MONTH(`Period`)*4 )) PARTITIONS 24
This create a series of 24 partitions, each containing 3 consecutive months of data, spanning a six year period. Any query accessing a single quarters data (Jan to Mar) would only access one partition. The majority of writes were also against a single partition.
As the years rolled on, and wrapped back to the beginning, the early partitions contain more data than the rest, until the earliest year of data is culled from the table, causing them to drop back to the same rough row-counts as their siblings.
Hope this helps someone?