MySQL Forums
Forum List  »  Partitioning

Re: Partition Type
Posted by: Phil Hildebrand
Date: March 23, 2008 09:48PM

Assuming you can't alter the id's to be integers, I would probably
try adding creation date to the primary key

primary key (id,creationdate)

then range partitioning by date (assuming more resent info gets the most usage)

Note: using range partitions with dates requires maint in order to keep useful
knowledge of what's in a partition. If you dont' need that, you could
use a hash partition, with 12 partitions.

alter table selling_trigger_story_hit
partition by range (extract(year_month from creationdate))
(
partition p_archive values less than (200701),
partition p_lastyear values less than (200801),
partition p_cur_jan values less than (200802),
partition p_cur_feb values less than (200803),
partition p_cur_mar values less than (200804),
partition p_cur_apr values less than (200805),
partition p_cur_may values less than (200806),
partition p_cur_jun values less than (200807),
partition p_cur_jul values less than (200808),
partition p_cur_aug values less than (200809),
partition p_cur_sep values less than (200810),
partition p_cur_oct values less than (200811),
partition p_cur_nov values less than (200812),
partition p_cur_dec values less than (200901),
partition p_future values less than (MAXVALUE)
)

If monthly data is still too much per partition, then you could subpartition
via hash by day (not exactly precise, as there are not always 30 days in a month)

Note: this will create about (15 * 30 paritions), alot, but still within the
1024 limit.

alter table selling_trigger_story_hit
partition by range (extract(year_month from creationdate))
subpartition by hash (dayofmonth(creationdate))
subpartitions 30
(
partition p_archive values less than (200701),
partition p_lastyear values less than (200801),
partition p_cur_jan values less than (200802),
partition p_cur_feb values less than (200803),
partition p_cur_mar values less than (200804),
partition p_cur_apr values less than (200805),
partition p_cur_may values less than (200806),
partition p_cur_jun values less than (200807),
partition p_cur_jul values less than (200808),
partition p_cur_aug values less than (200809),
partition p_cur_sep values less than (200810),
partition p_cur_oct values less than (200811),
partition p_cur_nov values less than (200812),
partition p_cur_dec values less than (200901),
partition p_future values less than (MAXVALUE)
)

Options: ReplyQuote


Subject
Views
Written By
Posted
3571
March 22, 2008 02:23AM
2888
March 22, 2008 10:19PM
2554
March 23, 2008 10:40AM
Re: Partition Type
2999
March 23, 2008 09:48PM


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.