MySQL Forums
Forum List  »  Newbie

Re: count rows in a partition
Posted by: Rick James
Date: July 30, 2013 08:54PM

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.

Options: ReplyQuote


Subject
Written By
Posted
Re: count rows in a partition
July 30, 2013 08:54PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.