what is more costly, 2 updates, or a delete and an insert?

Question

For a certain piece of business logic, a sequence could be either two separate updates, or a delete followed by an insert. Basically, the update would nullify a column so I can make it seem like a delete. I am not concerned with losing rows (so a soft delete is not necessary): what is the more efficient approach?

Answer 1

This wildly depends upon the nature of the queries. Perhaps your two updates can be written as an update join and be faster than a delete and insert. If you have indices on your table, the delete and insert will be much more costly. If it's a variable length column, maybe the delete and insert is faster.

There are a lot of factors that go into it. The best bet is to benchmark it and use explain to figure out what, exactly, the database is doing with each of your queries.

Answer 2

It sounds like, from a data-integrity point of view, you'd be better off going with the delete and then insert.

And deleting data that you don't need is clearly less costly then keeping it.

In terms of actual speed, I would suggest it's negligible unless profiling proves otherwise.

Answer 3

Benchmark them. Don't forget to benchmark other queries on the table(s), too.

Better yet, pick whichever one leads to the most sensible data model.

Answer 4

Updating will typically be faster than deleting/inserting. http://stackoverflow.com/questions/824936/mysql-performance-delete-or-update

Answer 5

Best to measure, but it helps to understand what's happening at the disk level. Here's what happens conceptually:

If the updates are to columns that aren't in an index, then updates might be done "in place" in a single write unless the record grows (which, if you're soft-deleting a field, is unlikely). If the columns are indexed, there'll be writes to those indexes to remove the old values and add the new ones.

When you delete, there'll be a write to remove the primary key for the delete record from its index, and writes to remove any secondary keys from secondary indexes.

When you insert, there'll be writes for the record you're adding, one or more writes to get the primary key into an index (index pages might split, requiring multiple writes), and writes to get any secondary keys into their indexes.