Should I add a WHERE clause when updating with replacement rows
I want to perform a row replacement for an entire column, changing all instances of one phrase to another:
UPDATE `some_records`
SET `some_column` = REPLACE(`some_column`, 'foo', 'bar');
Since many lines do not contain the string "foo", they will not be affected by this query, which is good; I'm only worried about the lines that contain it. My question is, is there any reason to add a WHERE clause to explicitly target the rows to be affected? eg.
UPDATE `some_records`
SET `some_column` = REPLACE(`some_column`, 'foo', 'bar')
WHERE `some_column` LIKE '%foo%';
As far as I can tell, both queries have the same effect. Are there any benefits for the 2nd version? Does it provide better performance or other benefits? Until I found the documentation to say that one is better than the other.
source to share
If there is a trigger BEFORE
/ AFTER UPDATE
defined on the table, the difference in queries is whether the trigger is fired for all rows in the table or only for rows that satisfy the predicate in the WHERE clause.
Otherwise, the two queries are equivalent in MySQL. MySQL does not count (or report) a row as "affected" by the UPDATE if the value assigned to the column is identical to the value already specified in the column. (Other relational databases count such rows in a "corrupted" counter.
Because of the leading percent sign in the LIKE comparison, this condition will need to be evaluated for every row in the table, so there will be no performance difference. If there is an index on some_records (some_column), MySQL may opt for a full index scan, which may be slightly faster in some cases.)
If you are familiar with other relational databases (Oracle, SQL Server, etc.) then adding a clause WHERE
is second nature.
Aside from these issues, it doesn't matter if you add the WHERE clause or not.
The reasons I could see worrying about adding a WHERE clause:
- avoid startup
BEFORE
/AFTER UPDATE
triggers - familiar pattern used in other relational databases
- possibly improved performance (if the strings are very long, if the index is much, much shorter, and a small fraction of the strings will satisfy the condition)
source to share
AFAIK, if you have an index on a column that is used as a condition in the WHERE clause, it should speed up the search for rows that need to be updated.
If you don't have a where clause, the default database reads all rows from disk and then replaces. For strings that are not suitable for replacement, this is an unnecessary search from disk.
source to share