mysql deletion efficiency

Question

I have a table with large amount of data. The data need to be updated frequently: delete old data and add new data. I have two options

1. whenever there is an deletion event, I delete the entry immediately
2. I marked delete the entries and use an cron job to delete at unpeak time.

any efficiency difference between the two options? or any better solution?

Answer 1

• Both delete and update can have triggers, this may affect performance (check if that's your case).
• Updating a row is usually faster than deleting (due to indexes etc.)

However, in deleting a single row in an operation, the performance impact shouldn't be that big. If your measurements show that the database spends significant time deleting rows, then your mark-and-sweep approach might help. The key word here is probably measured - unless the single deletes are significantly slower than updates, I wouldn't bother.