Re: count rows in a partition
Sliding partitions (PARTITION BY RANGE(date))...
To have a year's worth of data, I would have, partitions for
'Start', July2012, Aug2012, Sep2012, ..., July2013, 'Future'
Tomorrow (which is Aug 1), I would do
REORGANIZE Future INTO Aug2013 and Future (I'll explain in a moment), and
DROP PARTITION July2012.
I have 15 partitions today, and I will still have 15 tomorrow.
To explain the weirdnesses...
* 'Start' is for catching illegal dates. (You may notice in EXPLAIN PARTITION with a date/datetime, that it always includes the first partition. By having an empty first partition, there is little loss of efficiency. 5.6 has a workaround.)
* I need 13, not 12, monthly partitions, in order to hold all data for the past 365 days.
* 'Future' exists as a way to catch any records if the REORG+DROP script fails to run. (This saved me, big time, once.)
* DROP PARTITION is 'instantaneous'.
* "Range" scans, such as "WHERE date >= '2013-07-05' AND date < '2013-07-15' will do pruning. In this example, it will hit only July2013 (and Start).
> the database growing and growing on the disk
Note that I always have 15 partitions and 12-13 month's worth of data.
PARTITION BY (MONTH(date)) and 365-day requirement...
* To keep 365 days' data, you would be writing to the July partition before last July's data is purged.
* Purging implies a DELETE, which is much more costly than DROP.
* Range scans will _not_ do _any_ pruning; _all_ partitions will be checked. I'm not even sure this will prune: "WHERE MONTH(date) = 7 AND ...".
* The need for Start and Future goes away; you need exactly 12 partitions.
PARTITION BY (MONTH(date)) and you need no more than 11 months' data...
* You could (monthly) DROP PARTITION + ADD PARTITION. Eg, sometime this month (July), you would drop and recreate the July partition. But if the script failed to run, you would be stuck with an ugly DELETE.
* Pruning is still a problem.