CODEWITHSUNDEEP
mysqloptimization
Baumannzone
Baumannzone

Reputation: 780

MySQL update 1 query or multiple querys

I have a update query (mysql) and for testing reasons I am using multiple update querys instead only 1.

I mean:

UPDATE table1
SET col_name1=expr1
WHERE id = 1;

UPDATE table1
SET col_name2=expr2
WHERE id = 1;

etc

This way, I can check and test better than if I make a multiple value update query.

But, after finishing tests, what should i do? Keep it? Or improve my query to 1 update query?

I believe second option is better regarding optimization, right?

Thanks.

Upvotes: 0

Views: 49

Answers (2)

sqlab
sqlab

Reputation: 6436

Of course use only one UPDATE statement. The performance is better, otherwise it will use n timesthe time of transaction

Upvotes: 1

Racil Hilan
Racil Hilan

Reputation: 25351

Well, it is pretty simple and straightforward matter. Let's take an example from real life:

Let's say I send you emails asking you to do things. Something like:

  • 10:00 - Please go to kitchen.
  • 10:05 - Please take a cup from the cupboard.
  • 10:07 - Please place the cup in the coffee machine.
  • 10:10 - Please press the start button on the coffee machine.
  • 10:15 - ...

I'm sure you will get tired very soon going back and forth between your desk to read the new email and then to the kitchen to do what I asked you to do. Assuming you're super kind and you're still willing to do what I ask you to do (or maybe I'm your boss and you have no choice :-)), I'm sure you will then just send me an email like this:

  • 10:16 Could you please send me all what you want in one email and I will follow your instructions?

This is exactly what you're doing with the database. It is absolutely much better to send one full update query than several small ones.

Upvotes: 2

Related Questions