MySQL Forums
Forum List  »  Partitioning

Re: Update and pruning
Posted by: Mikael Ronström
Date: November 13, 2006 02:13PM


Jens Tandstad Wrote:
> I have a very straightforward question:
> I have table partitioned by parentID. If I change
> the parentID and the new parentID requires the row
> to be moved to a different partition. Does this
> happen automatically if i use the SQL below?
> UPDATE person.parentID = 22 where parentID = 10;

Yes, this happens automatically. As MySQL works it is known
from which partition to update comes and then before applying
the update the partition to put the updated version into is
calculated. If the same an update is performed otherwise an
insert into the new one is performed followed by a delete from
the old partition.

Check ha_partition::update_row in for details.

Defragmentation services isn't part of the partitioning services.
This is a service of the underlying engine if it exists.

Rgrds Mikael

> Does the system identify if the row is now in
> violation with the partition it is currently in,
> and move it? If yes, is the previous partition
> reordered (defragmented) or is there an empty row
> in it? And is there a defragmentation function for
> this kind of situtation that can be scheduled for
> running during the night. I have a system where
> that requires fast access to data (pruning helps)
> but it also requires a great deal of updating.
> I am grateful for any help.
> Regards
> Jens

Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:

Options: ReplyQuote

Written By
November 12, 2006 08:48AM
Re: Update and pruning
November 13, 2006 02:13PM
November 14, 2006 08:47AM
November 14, 2006 07:09PM
November 18, 2006 01:02PM

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.