Clever Geek Handbook

Upgrade versus choice

I am using MySQL as my database. I need to update some data. However, the data may not change, so I may not need to update the row in this case.

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

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

b) Use the UPDATE query directly. If there is no change to the data, MySQL will automatically ignore it and not process data updates.

So which one would work best in this case.

+3


source to share


3 answers


From the MySQL manual :

If you set a column to the value it currently has, MySQL notices it and doesn't update it.



So, keep latency and leave this task to MySQL. It will even tell you how many lines were affected.

+3


source


The first options seem to me better, but in a specific scenerio.

  • Select all or more rows from the table and get them in the result set
  • Walk through the result set as memory traversal is really fast enough and select the primary keys whose records you want to update and then run update queries.


It seems to me a comparatively efficient solution for me compared to doing an update request on every row regardless of whether it needs to be done or not.

0


source


If you are using the select-then-update approach, you need to lock the row (for example, select to update), otherwise you are in a race condition - the row can be changed after you have selected and verified that it cannot be changed.

As @AndreKR pointed out, MySQL will not perform any write operation if the values ​​are the same, so using update

is faster than using 2 queries.

0


source






More articles:


All Articles