MySQL Forums
Forum List  »  Partitioning

HOw is in partitioning by range implemented the UPDATE statement ...
Posted by: Mariella Di Giacomo
Date: September 09, 2008 11:46PM

Hello,

I have a table partitioned by range on a datetime field using to_days function
The table is Innodb type and the server creates a file per each InnoDB table.

create table sort_table (
id int(10) unsigned NOT NULL,
info1 int(10) unsigned NOT NULL,
....
sort datetime NOT NULL,
KEY (id)
) ENGINE InnoDB DEFAULT CHARSET=utf8 PARTITION BY RANGE (to_days(sort)) (PARTITION p20080 ..........

The table has several partitions (365) and each partition contains all the data related to a specific day.

E.g. on day 2008-09-01 there is a row that looks like
INSERT INTO sort_table (id,info1,sort) VALUES (100,200,'2008-09-01 00:01:01');
THat record gets inserted into the partition that matches the day '2008-09-01'

NOw on the day '2008-09-09' another record with the same id
should be inserted into the partition that matches '2008-09-09'

1)
One approach for doing that is doing an INSERT in the specific partition
INSERT INTO sort_table (id,info1,sort) VALUES (100,200,'2008-09-09 01:02:02');

This will generate a record in the partition matching the date '2008-09-01'
and one in the partition matching the date '2008-09-09'

2)
The second approach could be instead of doing the second INSERT,
execute an UPDATE

UPDATE sort_table SET sort='2008-09-09 01:02:02' where id=100 and sort= '2008-09-01 00:01:01'
(provided that I know the old exact date)

This UPDATE statement will I guess INSERT into the partition matching '2008-09-09' and deleting the record from the partition matching the date '2008-09-01'

Which is the overhead of this case compared to 1) ?
HOw is exactly this type of UPDATE implemented in partitioning ?
Does the record get physically removed or just marked for deletion ?
Would this cause fragmentation on the partition with the oldest date ?

Thanks in advance for your help,

Mariella

Options: ReplyQuote


Subject
Views
Written By
Posted
HOw is in partitioning by range implemented the UPDATE statement ...
4287
September 09, 2008 11:46PM


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.