MySql update query - Update column by string replacement in all records

Problem Statement

In a mysql table, I wanted to replace the hostname of the image URLs. And, it will be an update statement which will update all such records.

Query

update `TABLE_NAME` set COLUMN_NAME = REPLACE (COLUMN_NAME, 'STRING_TO_SEARCH', 
'STRING_TO_REPLACE_WITH') WHERE COLUMN_NAME like '%STRING_TO_SEARCH%';

In above query:

• TABLE_NAME is thr name of your table
• COLUMN_NAME is the name of your column which you want to update
• STRING_TO_SEARCH is the string that you want to search and replace with something else
• STRING_TO_REPLACE_WITH is the target string with which you want to replace the search string

Performance Optimization

Note that in above query, where clause is suppose to filter out the target set of strings on which you want to work upon.

• First where clause will do its work to perform string match, and filters out the matched records. Note: it is running on all the records
• Second, REPLACE will run on all the filtered records.

Now, if you notice that since REPLACE function will anyway has to do the search operation, what is the need of where clause. In reality, it is actually slowing down the system. Its not optimized. You can simply remove the where clause, and do something like:

update `TABLE_NAME` set COLUMN_NAME = REPLACE (COLUMN_NAME, 'STRING_TO_SEARCH', 
'STRING_TO_REPLACE_WITH')