(My)SQL performance: updating one field vs many unneccesary fields

Question

i'm processing a form that has a lot of fields for a user who is editing an existing record. the user may have only changed one field, and i would typically do an update query that sets the values of all the fields, even though most of them don't change. i could do some sort of tracking to see which fields have actually changed, and only update the few that did. is there a performance difference between updating all fields in a record vs only the one that changed? are there other reasons to go with either method? the shotgun method is pretty easy...

Answer 1

I would say that it depends upon the following:

• The size of the data that is being processed
• The location of the database server relative to the application
• The time taken to complete any checks for data change

If you are transferring large amounts of data and/or the connection is remote then you should do some tests to see if you can improve performance by tracking changes. Otherwise, you will probably find that it is negligible presuming that one record is being manipulated.

Answer 2

I'd say go for the shotgun but it really depends on many things and the use you have on the db.

• One user or millions concurrent users?
• Are the fields small or there are large text/blob fields too?
• Is the application where the form is filled near the db or through a network or the web?

You have to take into account that the UPDATE will have to not only store the new (even the unchanged ones) fields into the table but also:

• check foreign key constraints
• update all indexes on updated fields

In all cases however, you can implement the easy way and test for any performance issues to be safe.

Answer 3

If you are talking about a reasonable amount of data (e.g. 1kb+) then optimizing could be worthwhile. If this statement/table is being run/updated frequently (several times a second?), by multiple users, etc it may be worth optimizing.

You should already have a copy the original data, so figuring out what has changed is not a big problem, and neither is changing the update statement to accommodate only the changed fields.

So it may not be a bad idea, but unless you are looking to save bandwidth, or feel you need to improve performance, it's probably not necessary.