jack
jack

Reputation:

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

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?

Upvotes: 0

Views: 1881

Answers (5)

Dave W. Smith
Dave W. Smith

Reputation: 24966

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.

Upvotes: 1

pbreitenbach
pbreitenbach

Reputation: 11291

Updating will typically be faster than deleting/inserting. MySQL performance DELETE or UPDATE?

Upvotes: 1

derobert
derobert

Reputation: 51167

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.

Upvotes: 1

Noon Silk
Noon Silk

Reputation: 55112

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.

Upvotes: 0

Eric
Eric

Reputation: 95153

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.

Upvotes: 0

Related Questions