CODEWITHSUNDEEP
mysqldatabase-performance
mirza
mirza

Reputation: 5793

Is it better to use deactive status column instead of deleting rows for mysql performance?

Recently i watched a video about CRUD operations in mysql and one of the things comes to my attention in that video, commentator claimed deleting rows bad for mysql index performance instead of that we should use a status column.

So, is there a really difference between those two ?

Upvotes: 4

Views: 1171

Answers (2)

Ami
Ami

Reputation: 1254

I guess the strategy is that deleting a row affects all indexes, whereas modifying a 'status' column might not affect any indexes (since you probably wouldn't index that column due to the low cardinality).

Still, when deleting rows, the impact on indexes is minimal. Inserting affects index performance when it fills up a page, causing the index to be rebuilt. This doesn't happen with deletes. With deletes, the index records are merely marked for deletion.

MySQL will later (when load is low) purge deleted rows from the indexes. So, deletes are already cached. Why double the effort?

Your deletes do need indexes just like your selects and updates in order to quickly find the record to delete. So, don't blame slow deletes that are due to missing or bad indexes on MySQL index performance. Your delete statement's WHERE clause should be able to utilize an index. With InnoDB, this is also important to ensure that just a single index record is locked instead having to lock all of the records or a range.

Upvotes: 0

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230481

Deleting a row is indeed quite expensive, more expensive than setting a new value to a column. Some people don't ever delete a row from their databases (though it's sometimes due to preserving history, not performance considerations).

I usually do delayed deletions: when my app needs to delete a row, it doesn't actually delete, but sets a status instead. Then later, during low traffic period, I execute those deletions.

Some database engines need their data files to be compacted every once in a while, since they cannot reuse the space from deleted records. I'm not sure if InnoDB is one of those.

Upvotes: 2

Related Questions