3

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?

  • 3
    Don't enclose numbers in single quotes, that's for strings. – sticky bit Jul 27 '18 at 11:14
3

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).
  • 3
    @EzLo are you answering yes to the question about using a pre-UPDATE SELECT statement, or yes to the question about including an extra condition in the UPDATE statement's WHERE clause? Or are you saying he should do both? – DoctorDestructo Jul 27 '18 at 19:44
5

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

5

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.

2

It is not worth doing a SELECT beforehand and could even decrease the performance of your System. Your RDBMS needs to do this SELECT (searching the row that needs to be updated) anyway when you UPDATE. The UPDATE itself is (compared to the search) negligibly fast. Costs and overhead (network latency + your check whether to update or not) is for two operations (SELECT + UPDATE) much, much higher than simply call UPDATE alone.

It was stated, you would benefit from indexes. This is not the case, because the first (and only) time you set 0 to 1 the index got already rebuilt.

2

Assuming

  • No TRIGGERs
  • No Replication
  • id is the PRIMARY KEY of the table; that is, only one row matches id=100 and id is indexed.

Then

  • Do not do the SELECT -- it is doing something that the UPDATE will do.
  • Do not do the SELECT -- KISS.
  • AND lock='0' is optional since the id=100 is sufficient to limit the filtering to a single row. (If you expected multiple rows to match, then the AND would probably be beneficial, and it might benefit from an index starting will lock.)

Think of it this way:

  • All proposed formulations require looking at one row.
  • Fetching a row to look at it is a moderately costly.
  • Two queries is more costly than one.
  • Your programming time is even more costly, so KISS.

A side note (addressing one of the Comments):

  • id = '100' is turned into id = 100 during parsing; so no significant performance difference. The index (eg, PK) on id is used in either case.
  • varchar_col = 100 is bad; every row must be looked at to see if varchar_col converts to the number 100. No index on varchar_col is not useful.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.