such as a table A
create table A(, id int NOT_NULL AUTO_INCREMENT, is_deleted tinyint(1) NOT_NULL DEFAULT 0, PRIMARY KEY (id) )
It uses the
is_deleted column instead of the delete statement
But there is another case, which is to put the deleted data into a separate record table
create table ARecord( id int NOT_NULL AUTO_INCREMENT, delete_time datetime, PRIMARY KEY (id) )
The deletion here refers to the deletion in the macro sense.
i.e. expired, invalid, graduated, dismissed.
Essentially a huge drop in lookup frequency
So what are the use cases for these two? When to use column? When to use table?
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
(This Answer is based on Experience, not just Opinion.)
Cases for “soft” delete (flag
is_deleted, or even
deleted DEFAULT NULL)
- The table is “small” — under, say, 50K rows and/or
- The number of “deleted” rows is low — under, say, 1/4 of the rows.
Else move the “deleted” rows to another table.
A 3rd approach is to
PARTITION on the deleted column. But this is not necessarily any better than your 2-table approach.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0