MySQL: is Partitioning a good way to handle deletes?

I have an MySQL 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 VAL开发者_开发技巧UES 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.

Yes, I'd expect the transition between the two states to be slower for sake of being passed between the partitions. But deleted/non-deleted queries on existing values would be faster, though queries that don't involve the deletion status wouldn't be improved.

It's all about what is the most common operation in the table, and accepting that there can be compromises.

I've been on a project in the past where this approach was used, and my personal feeling is that it's not the best approach. I think it's better to just delete the records. When you have a flag like this, everyone who uses your database has to understand that records that exist in the table might not be "real" records, depending on whether or not the deleted flag is set. It just makes the database less intuitive, and harder to use in my opinion.

If you're concerned about performance, I'd look at properly allocating your tablespaces, and you can still employ a partitioning scheme. You could partition the data by year and month (if you need that level of granularity) to help with the performance.

But I would avoid the deleted flag. On the project I worked on, it just became a real headache. For example, what if somebody tries to insert another record exactly the same as the one that was "deleted" (deleted here means deleted flag is true). Do you set deleted to false on the existing record, or do you insert another brand new record? If you insert a brand new record, how do you define your primary key on the table since now you have 2 records with the same key? Do you make deleted part of the key? The point is that you have to deal with all those types of non-trivial issues.