CODEWITHSUNDEEP
mysqldatabaseperformancesql-update
Tommy
Tommy

Reputation: 75

Multiple updates or update ... where in

which version is better (performance)?

1. update my_table set my_col = 1 where my_id = 100
   update my_table set my_col = 1 where my_id = 110
   update my_table set my_col = 1 where my_id = 120

2. update my_table set my_col = 1 where my_id in (100, 110, 120)

Upvotes: 1

Views: 284

Answers (1)

Nachiket Kate
Nachiket Kate

Reputation: 8571

In your case, both ways will have almost equal response time as you are running only 3-4 queries.
But definitely 2nd way will be faster for higher number of queries or updates (bulk updates are faster) because this will reduce,

  1. creating,binding connections to database
  2. query compilation/optimization task of sql engine

but bulk updates has one downside also i.e. locking of tables as you are updating multiple records in single statement, table will be locked for that much duration. so perform bulk updates with considering acceptable locking period in mind.

Upvotes: 1

Related Questions