5

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

DELETE the Row? INSERT| -> DELETE -> INSERT .. DELETE -> INSERT

or

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

Community
  • 1
Toleo
  • 318
  • 1
  • 4
  • 13

2 Answers2

6

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.

Hannah Vernon
  • 70,041
  • 22
  • 171
  • 315
4

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.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520