UPDATE vs DELETE and INSERT

Question

In SQL, is UPDATE always faster than DELETE+INSERT?

I have read for this thread that UPDATEs and more efficient than INSERTs and DELETEs I am contemplating to UPDATE a row to NULL values apart from the primary key ID instead of DELETING the row Then any new data can simply populate that row with the kept primary_key ID, what are the potential problems with this and is this a good approach to efficiency?

That would require two updates instead of one delete and one insert. Since each update would have to change multiple column values, the delete/insert pair is likely to be faster. Additionally, all those "empty" rows would potentially need to be parsed on every select query, which could slow those queries down relative to the table without them. — Nick, Dec 22 '19 at 02:07
Does have to be updating all fields to NULL values, just maybe one field called stale — , Dec 22 '19 at 02:16
"Premature optimization is the root of all evil." To be honest, there is evil in the world not related to premature optimization. But much of that is not in the realm of computers. — Gordon Linoff, Dec 22 '19 at 03:54
Like Gordon and Strawberry already made comments about your question is about if SQL defaults into storing in a fixed order in a file which it does not well some storing engines in MySQL do — Raymond Nijland, Dec 22 '19 at 08:23

score 2 · Accepted Answer · answered Dec 22 '19 at 02:39

The approach normally depends on where you have an OLAP (analytical) or OLTP (transactional) application.

In an OLTP application generally multiple records of a table are concurrently altered under multiple transactions and each transaction is small. Here you should update the records by their PK or any other key having an index.

In an OLAP application generally a large part of the table is altered during ELT by one (or sometimes few non-overlapping) transaction(s). Here delete+insert is more efficient. You would typically be altering many column values so in one shot the whole row can be changed. Since large number of rows are altered, a full table scan is preferred over index access.

score 1 · Answer 2 · answered Dec 22 '19 at 02:47

Unless you are building an application at massive scale and you have already observed that database performance for this particular query is a bottleneck, and you have already verified the performance gain from this scheme, don't overthink this.

MySQL is really good at making your queries work efficiently in almost all cases. There are times and places you need to optimize so heavily that you might need to check whether repurposing existing rows for new records is going to squeeze out a little bit more efficiency, but if you were in one of those times and places you would already have done the benchmarking on this.

If you don't have a compelling reason, use DELETE to delete stuff, INSERT to insert stuff, and UPDATE to update stuff.

The initial conditions of a dynamic system matter – Dec 22 '19 at 06:25 — , Dec 22 '19 at 06:25

score 0 · Answer 3 · answered Dec 24 '19 at 00:46

One statement is faster than two. (Usually.)
Any of INSERT, UPDATE, DELETE must juggle things in the block where the row is.
There is no advantage in having an "empty" row awaiting a possible replacement.
Eschew "premature optimization".
See also `INSERT ... ON DUPLICATE KEY UPDATE ..." (aka "IODKU" or "upsert")

UPDATE vs DELETE and INSERT

3 Answers3