MySQL Forums
Forum List  »  Partitioning

Re: Is Partitioning a good way to handle deletes?
Posted by: Mattias Jonsson
Date: November 08, 2010 06:19AM

Hi John,

You are correct that, within the partitioning handler, it will need to move the row from one partition to the other, which is done by an insert into the new partition and a delete from the old partition internally.

But since you have PRIMARY KEY (id) you cannot partition by 'deleted', the primary key must include all columns in the partitioning function:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-partitioning-keys-unique-keys.html

If you use MySQL 5.5's new features COLUMNS partitioning and TRUNCATE PARTITION, you could try something like:
CREATE TABLE `responses` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`other_id` int(11) NOT NULL,
`details` text,
`deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`,`deleted`)
)
PARTITION BY RANGE COLUMNS(deleted,id)
(PARTITION pActive VALUES LESS THAN (1,0),
PARTITION pDeleted VALUES LESS THAN (MAXVALUE,MAXVALUE))

And use ALTER TABLE responses TRUNCATE PARTITION pDeleted, to finally remove the deleted rows.

If your idea is to always mark rows as deleted and as some kind of maintenance, export/archive/delete the marked rows. Then using partitioning would give you a much faster operation (since you can use DROP PARTITION or TRUNCATE PARTITION, which is very fast operations, note however that they differ in transactional behavior from an ordinary delete, since they cannot be rolled back). But it could be at the expense of the update of the deleted column. But you have to benchmark this your self since it depends on the engine you use etc (I don't think it would be that big penalty).

In the development branch for MySQL (5.6) we have added the possibility to EXCHANGE PARTITION WITH TABLE which would match your archive step very good:
http://dev.mysql.com/doc/refman/5.6/en/partitioning-management-exchange.html


So if you plan to do mark rows as deleted even without partitioning, I would suggest you to try partitioning! This way you should get much faster 'Delete all marked rows' operation.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Is Partitioning a good way to handle deletes?
1184
November 08, 2010 06:19AM


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.