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

i'm processing a form that has a lot o开发者_如何学Gof 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...

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.

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.

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.

You might actually lose performance by only updating single fields. If you're using prepared statements, then the database has already compiled the query plan for updating all fields. If you start updating random fields, then you'll have to parse the query every time you update, losing the benefit of the prepared statements. I'm not sure how much effect this would have in MySQL, but I know it can be significant with SQL Server and Oracle.

There would of course be some overhead in updating multiple fields, but the overhead will be minor in comparison to the cost of maintaining state about what fields changed. If this is 1 table, I would not worry about selectively updating columns based on the state of your form.

Also, it may be useful to look at a database framework like Hibernate to abstract some of these details.

I would go for the shotgun method ad it is easy to understand and implement. The performance hit should be neglectable.