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)