CODEWITHSUNDEEP
mysql
doubl3
doubl3

Reputation: 9

mysql delete/insert vs. update

I have tried to find thoughts on this, but can't find quite what I am looking for.

In one topic on here, people seemed to agree that "almost" all of the time, an update would be much preferable to deleting a row and re-inserting it.

HOWEVER, my situation is which would be better between doing several hundred individual updates vs. 1 mass delete and 1 bulk insert for those hundreds of rows.

Wouldn't all of the time saved from doing the bulk insert more than offset the extra work from doing the delete/insert method vs. update?

No other table needs the ids from these rows, by the way.

Upvotes: 0

Views: 722

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

I think the answer would depend on the exact update/delete query you are trying to run, and on the data. But, in general I would expect that just doing an update would be faster than deleting and re-inserting. The reason is that very similar lookup logic will have to run in either approach to target the records in question. In the case of delete/insert, you would then remove those records and bulk insert. However, in the case of update, you would have already found the records, and would just need to mutate some data.

Upvotes: 2

Related Questions