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

You should use low_priority_updates - http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_low_priority_updates. This will give higher priority to your selects than insert/delete/update operations. I used this in production and got a pretty decent speed improvement. The only problem I see with it is that you will lose more data in case of a crashing server.

Answer 2

• 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.