MySQL Forums
Forum List  »  Partitioning

Is Partitioning a good way to handle deletes?
Posted by: John Smith
Date: November 06, 2010 05:34PM

I have a table:

CREATE TABLE responses (
id INT NOT NULL AUTO_INCREMENT,
other_id INT NOT NULL,
details TEXT,
deleted BOOLEAN,
PRIMARY KEY (id)
);

Users could delete records in `responses`.

My plan is to use the field `deleted` to implement deletion. Any time a user deletes a record, I'll set `deleted` to `1`.

Every now and then I might want to get rid of all the deleted records or to archive them. I'm thinking of using partitioning to make that faster:

PARTITION BY LIST(deleted) (
PARTITION pActive VALUES IN (0),
PARTITION pDeleted VALUES IN (1)
);

My question is would this make the act of deletion slower? Now that when I change a record's "deleted" field, MySQL would need to move the record to an entirely different partition. That seems like it could be slow.

Any suggestions would be greatly appreciated.

Options: ReplyQuote


Subject
Views
Written By
Posted
Is Partitioning a good way to handle deletes?
2131
November 06, 2010 05:34PM


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.