CODEWITHSUNDEEP
mysqlperformanceselect
Dell User
Dell User

Reputation: 33

Update versus Select

I am using MySQL as database. I need to update some data. However the data may haven't changed so I may not need to update the row in such case.

I wanted to know which one will be better (performance wise):

a) Search the table to determine if the data has changed. For example I can search by primary key and then see if the value of remaining fields have changed or not. If yes, then continue with update statement and if not then leave it.

b) Use UPDATE query directly. If there are no changes in the data, MySQL will automatically ignore it and not process updating the data.

So which one will be perform better in such case.

Upvotes: 3

Views: 998

Answers (3)

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26699

If you use the select-then-update approach, you need to lock the row (e.g. select for update), otherwise you are in a race condition - the row can be changed after you selected and checked that it hasn't be changed.

As @AndreKR pointed out, MySQL won't perform any write operation, if the values are the same, so using update directly is faster than using 2 queries.

Upvotes: 0

AndreKR
AndreKR

Reputation: 33678

From the MySQL manual:

If you set a column to the value it currently has, MySQL notices this and does not update it.

So save yourself the latency and leave that task to MySQL. It will even tell you how many rows were actually affected.

Upvotes: 3

Mian Zeshan Farooqi
Mian Zeshan Farooqi

Reputation: 311

First options seems better to me but in a specific scenerio.

  • Select all or some rows from table and get them in a result set
  • Traverse the result set, as in-memory traversal is really fast enough, and pick up the primary keys whose records you want to update and then execute update queries.

It seems comparetively efficient solution to me as compared to executing update query on each row regardless whether you need to do so or not.

Upvotes: 0

Related Questions