Monday, October 17, 2005

Partitioning on non-PK field

An interesting comment from Timo mentioned that he wanted to have
partitioning made on a non-PK field. The problem with this is that it
requires a global index on PK to be able to support the primary key
constraint on the table. Global indexes on partitioned tables will not
be supported in version 5.1.

What could be done here is to change the PK into a composite index
with the original PK fields + the partitioning field. Thus we get both
partitioning based on the field required and still have the PK constraint
possible to maintain.

If this is done then voila, the query will only use the server where the data
resides and won't need to access all parts.

Also dynamic repartitioning and possibility to add nodes online is a very cool
feature that would be nice to have. It is certainly on the roadmap but as
usual it is dependent on priorities in which version it actually arrives in.

1 comment:

pdrapeau said...

I have been reading through the MySQL documentation recently, and noted in 5.1 the alter table syntax includes functionality to alter partitions. apparently it indicates that its possibable to do all functions except for dropping a parition, does this mean that the partitioning in NDB type databases can be changed on the fly to say, add new partitions.

the direct quote from the docs is as follows

It is not possible to drop partitions from NDB tables using ALTER TABLE ... DROP PARTITION. The other partitioning extensions to ALTER TABLE — ADD PARTITION, REORGANIZE PARTITION, and COALESCE PARTITION — are supported for Cluster tables, but use copying and so are not optimised. See Section 18.3.1, “Management of RANGE and LIST Partitions” and Section 13.1.2, “ALTER TABLE Syntax”.

furthermore, I noted that under the NDB roadmap it indicates that it "is possible to partition tables based on KEY, HASH, RANGE, and LIST, as is subpartitioning"

I note elsewhere in the manual that its stated that

"You can also use an SQL expression that returns an integer for expr. For instance, you might want to partition based on the year in which an employee was hired. This can be done as shown here: "

for hash partitioning, does this mean thats also supported on NDB?