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

  • 1
    A thousand rows? The choice doesn't matter much. A million rows? It may matter. Use what is cleaner in your app code. – Rick James Mar 7 '18 at 15:52
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.

  • 1
    using is_deleted column in Query WHERE is_deleted = 0 would be faster Because it will use INDEX and will Ignore deleted rows, Correct? – Toleo Mar 2 '18 at 15:01
  • 5
    Yes, there are a variety of strategies where an is_deleted column will help performance. Note, however that any additional column will add overhead at the row level, and of course will consume some amount of disk space. As always, it's best to test, test, test. – Max Vernon Mar 2 '18 at 15:04
  • 1
    You should also consider that you now need two entirely different code paths for dealing with "creating" a new row. This may not be an issue, but in case there is some bug in the future that takes you a man week or two to find, you should document proof that it was too slow to do any other way. – Bill K Mar 2 '18 at 23:57
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.

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.