Is it worth checking value with select before updating

Well, "13.2.11 UPDATE Syntax" suggests, that no writing is done, when the value doesn't actually change.

If you set a column to the value it currently has, MySQL notices this and does not update it.

So the extended WHERE clause isn't needed to prevent unnecessary writes.

But you might get a tiny performance benefit from the extended WHERE clause as if not record to update was found, the logic to check, if the row actually needs to be changed won't run. But really, that's tiny.

And the effort for retrieval is there in either way. You can only try to support it with indexes (on id and lock (compound) for the extended version and on id for the other).


EzLo's got you covered with a good list of positives, and I'm going to toss out just one negative. I don't think it applies to YOUR case, but I'm recording this here for other folks who might read this answer later for their own uses.

If your query might update multiple fields, then there's going to be a cost to building all these different queries:

Say we're only updating someone's last name:

UPDATE dbo.Users 
  SET LastName = 'Smith' 
  WHERE id='100' AND LastName = 'Jones';

But say we're also updating their last name and address:

UPDATE dbo.Users 
  SET LastName = 'Smith',
      StreetAddress = '123 Main Street',
      City = 'San Francisco',
      State = 'CA'
  WHERE id='100' 
    AND LastName = 'Jones'
    AND City = 'New York'
    AND State = 'NY';

To build a query like that, you're going to need to do a lot of equality checking in your app code. Plus, to make matters worse, you're going to end up bloating the plan cache with lots of different update strings that get executed when different combinations of parameters are used - because sometimes we only update LastName, sometimes we update LastName and City, sometimes we update City and State but not LastName, etc.


Yes, it's worth for several reasons:

  • You might benefit for indexes on that column.
  • It might seem to be a pointless update for cases when it's already 1, but it might execute a trigger on the updating table.
  • Avoid logging additional rows from the update operation. Even if the previous value matches the new one, the database still logs the operation (different would be assigning the value of it's same column lock which would be skipped in this case).

Tags:

Mysql

Recent Posts