MySQL Forums
Forum List  »  Partitioning

Re: Partition Type
Posted by: Phil Hildebrand
Date: March 22, 2008 10:19PM

Can you put in your 'show create table' statement?

Currently, partitioning doesn't support FK's, so you'll probably have to drop that in order to partition the table.

If Entity ID is an integer, then your options are fairly open. You'll probably want to take into account how entity ID is used.

If you just want each entity ID to be part of a different partition, and entity id has a low cardinality (few entity id's), then a list partition might make the most sense, as you can control which entity id's go to which partitions.

LIST: assume 5 entity types:

alter table X partition by list (entity_id)
( partition p1 values in (1), partition p2 ....)

If entity id has a high cardinality, then it might make sense to use hash/key or range partitions. If entity ID is an auto increment, or something similar that has a somewhat predictable max value/distribution then maybe a range partition. If entity ID is grows a lot, and doesn't have a predictable max value, then a key/hash partition is nice because you don't need to know anything about the values of entity to create the partitions...

RANGE: assume up to 2000 entity types:

alter table x partition by range (entity_id)
( partition p1 values less than(500), partition p2 , ... partition p5 values less than MAXVALUE)

HASH/KEY: assume lots of entity types (unknown thousands)

alter table x partition by hash (entity_id) partitions 5

There are probably more technical ways to decide which makes sense for a given table/app, but these are my first conditions I look at when thinking about which way to partition.

Options: ReplyQuote

Written By
March 22, 2008 02:23AM
Re: Partition Type
March 22, 2008 10:19PM
March 23, 2008 10:40AM
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.