When reading this, it should be remembered that I’m an old geezer who remembers back when a megabyte of memory was a lot, and an actual hard drive was near priceless.

Up until recently, when I’d update a record, I would only update those fields which had changed from the original record. My logic was just along the lines of hitting the database as lightly as possible. So if a customer record was 25 fields and 3 were actually updated by the user, I’d figure out which three fields, and then only update those.

Of course, when you think about this, there’s actually quite a bit of infrastructure involved. Once you have your updated fields, you’ve got to fetch the current record, and compare each field to what was in the original record and determine what changed. “Blank” fields are particularly difficult, because of the way PHP determines blankness. Maybe the field contained content before, and the user actually blanked the field to erase it. You have to detect that and do a proper “blank” replacement for that field. But for that field, is it an empty string or a NULL? Or a bunch of spaces? It’s a lot of work doing it that way.

Now, I’m not an expert on the interior design of DBMS engines. I know most of them use some form of b-tree for indexing, and have a way of simply marking deleted records without actually removing them and recovering the space. But it has occurred to me that, one field or fifty, the time and effort it takes in the background to update a record may be almost uniform. In fact, I’m guessing that if you only update 3 fields, the engine probably marks the old record for deletion, copies any non-updated fields into a new record space, updates the 3 fields with the new content, and then stores the result. If you updated the whole record, then the load on the engine in replacing and updating fields might even be lighter.

It’s also worth noting that most update screens allow for everything (all fields) except the ID field (auto-incrementing) to be edited and updated. Meaning no gaps, whatever is on screen is going into the database, except for the submit button. That’s pretty much all the fields of the table.

So theoretically, it might be the same or less time spent by the DBMS engine updating records with fill full field contents as just a few fields. And it would certainly save me a lot of fetching, comparing with existing content, and then selectively updating. Just put all the fields together in one update statement, and let the engine have at it.

So it looks like I’ve changed my mind on that one. Aren’t you glad?