in this scheme

id  uid     content
1   1       A
2   1       B
3   1       C
4   1       D
5   1       E

If the User decided that he doesn't need the rows with id = 3, 4, 5 Is it better to make him set their content to NULL or just delete the row?

This process occur many time And he maybe use the NULL slots again. Do I still use UPDATE and set it to NULL in case he will change it in the future? Or just DELETE the entire row?

The question is like: is it better to



UPDATE the Row? INSERT| -> UPDATE to NULL -> UPDATE to value .. UPDATE to NULL -> UPDATE to value


If the rows will be re-used frequently, it is likely better to set the values to NULL, or simply add a column is_deleted to indicate that the row has been soft-deleted. Continuously deleting and inserting rows can lead to a heavily fragmented table, and can be a cause of performance degradation. This is typically used for queues where there are a known number of slots (rows) in the queue, and those slots are allocated ahead of time.

If the rows will not be re-used, or will be re-used very infrequently, then it's better to deallocate the space used by those rows.


In addition to MaxVernon's answer (hitting the nail on the head, +1 !!!), you could also consider using tombstone tables to monitor which rows can be soft deleted. See me post from Mar 05, 2012 Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios.

Please consider which way you can live with

  • Outright using DELETE will make a row get deleted and make the space available. The DELETE command will have to travel through the InnoDB Plumbing (locks, rollback segments, etc).
  • Using a delete flag would also be subject to InnoDB's management (less fragmentation)
  • Tombstone approach and delete flags put the strain of row deletion on application logic and a small code framework dedicated to it, whereas InnoDB has that baked in the Storage Engine

In the end, DELETE is the simplest and worry-free (or at least the most predictable) approach.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.