MySQL (InnoDB) UPDATE SET performance with value in WHERE AND clause

Question

If an UPDATE query is performed where the values being SET are also part of the WHERE clause, in the form of an additional AND statement on the column being set's value, is there a significant performance gain? Are there other benefits to updating a set of records in this way?

For example,

UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 != 'foo';

vs

UPDATE table1 SET value1='foo' WHERE value2='bar';

A more concise example:

+--------------------+-------------------------------------------------+------+-----+---------+----------------+
| Field              | Type                                            | Null | Key | Default | Extra          |
+--------------------+-------------------------------------------------+------+-----+---------+----------------+
| pk_id              | int(10) unsigned                                | NO   | PRI | NULL    | auto_increment | 
| user_id            | int(11)                                         | YES  |     | NULL    |                | 
| status             | enum('unread','accepted','deleted')             | YES  |     | NULL    |                | 
| processed_datetime | datetime                                        | YES  |     | NULL    |                | 
| some_column        | varchar(255)                                    | YES  |     | NULL    |                | 
+--------------------+-------------------------------------------------+------+-----+---------+----------------+

...with another example query:

UPDATE table1 SET status = "accepted", processed_datetime = NOW() WHERE pk_id = 1234 AND user_id = 5678 AND status != "accepted"

(Note: this is not my code / schema, someone else's that I am working with)

Answer 1

This very much depends on the data present in the table at the time of the UPDATE.

If, for instance, the table has 1,000,000 rows where value1 = 'foo' and only 1 row where value1 <> 'foo' then UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 <> 'foo'; will only actually update a single row. Whereas UPDATE table1 SET value1='foo' WHERE value2='bar'; would update 1,000,001 rows.

If you are talking about a table with only two rows, the difference is entirely negligible.

If you were using SQL Server and had a filtered index like WHERE value2 = 'bar' AND value1 <> 'foo' then UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 <> 'foo'; would most likely use the index instead of scanning the entire table.

Answer 2

In my opinion, having extra condition in WHERE makes no difference in queries like UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 != 'foo'; .

However, UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 = 'bar1'; is different. First of all, if you have an index on bar1 it will be used because UPDATE has read component and it has to locate record first. Secondly, it may be used to prevent 'lost update' problem to some extent, it looks to me like a sort of optimistic locking.