How much updating makes sense?

[phppup]

I'm creating a form that will contain between 12 and 20 fields.

After submitting and inserting into a db, a user can review a specific record (that uses SELECT * to repopulate the form's fields).

At this point, data can be changed and empty fields can be completed.

Here, the table will be updated.

The presumably "easy way" would be to now UPDATE the entire record (and I'm sure that would work), but it occurs to me, that only fields that have been changed actually need to be updated.

Is it more sensible and "proper" to only update fields that have changed? Or is it an unnecessary challenge?

Is the overhead that is being reduced really worth the effort? Or is the overhead incurred by comparing values negating the savings?

What is the best approach? I've seen articles indicating that JavaScript should do the comparison and then pass along the effected data.

Any thoughts or suggestions?

Thanks

 

Edited June 8, 2023 by phppup

[ginerjm]

Overhead?  For including some fields that weren't altered?  I don't think it is a concern at all.

[phppup]

Any other people have insight or opinions on this?

[Barand]

MySql does not make unnecessary changes

mysql> select * from user;
+----+-----------+----------+-----------+----------+
| id | firstname | lastname | user_name | password |
+----+-----------+----------+-----------+----------+
|  1 | Sarah     | Tonin    | saraht    | NULL     |
|  2 | Tom       | DiCanari | tomd      | NULL     |
|  3 | Laura     | Norder   | lauran    | NULL     |
|  4 | Anna      | Robik    | annar     | NULL     |
|  5 | Peter     | Dowt     | peted     | NULL     |
+----+-----------+----------+-----------+----------+
5 rows in set (0.04 sec)

mysql> update user set user_name = 'tomd' where id=2;

Query OK, 0 rows affected (0.71 sec)                   <<<<<<<
Rows matched: 1  Changed: 0  Warnings: 0               <<<<<<<

 

[ginerjm]

If you really think you have to do this then what you should do - since you have to validate any 'changed' values before using them - is to hide the original values in your form along with the visible input fields.  Then upon the submit check if an input value is new (id, <> original value) validate it and add it to the query.  If it is unchanged don't.  If a new value is not valid then create an error message and when done with this step either re-output the form or do the update using only the values you want to put into the query.

[kicken]

11 hours ago, phppup said:

I've seen articles indicating that JavaScript should do the comparison and then pass along the effected data.

You wouldn't do the comparisons in JavaScript, you'd do it in PHP when building your UPDATE query.  Select the current data from the DB, compare it with what you received, then UPDATE whatever has changed.

I wondered the same thing a while back, as this is something that Doctrine ORM does.  I use MS SQL Server and from what brief searching I found, it does make a difference there.  If I understand correctly, SQL Server like MySQL won't re-write the same data to disk, but it does record an entry in the transaction log.  As such, unnecessary writes can cause increased overhead when it comes to transaction log management / replication.

That said, I've never bothered doing this and instead just updating all the fields.  I believe this level of optimization isn't really necessary until you get to pretty large applications.  Adding the change detection complicates your PHP code and requires more processing overhead there so it's kind of a trade-off.  Adding overhead to your PHP code and lowering the load on the DB is preferred though, as it's easier to scale your application code processing than it is to scale your database server.

 

[mac_gyver]

the database server must find the row(s) being update. it then reads the data in those row(s), for two main reasons - 

1. if a column value doesn't change, it doesn't write the data back to the disk, skipping an operation. the database server performs this comparison for you, using the current 'locked' values from the row, in one ATOMIC operation, so that you are not potentially comparing new values with 'stale' data that could have been changed by some other concurrent operation on the data. 
2. so that you can perform things like SET count = count + 1 ... in a single query