Is it worth checking value with select before updating

I have a tinyint boolean value in my row that is 0 by default and 1 whenever a specific thing happens. After it's 1, it's always gonna stay 1.
Now that specific thing can happen for each row many times, but I only need to update the value to 1 once.

Is it worth doing a SELECT, check the value and only if the value is 0, trigger the UPDATE?

Also would maybe this

UPDATE table SET lock='1' WHERE id='100' AND lock='0'

be better than

UPDATE table SET lock='1' WHERE id='100'

for my situation?

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).

StackExchange Database Administrators Q#213397, answer score: 6