How to speed up a simple UPDATE of all values in one column, in MySQL-CodePudding

This is taking many hours on a table with over 4.6millon records.

Is there a way to speed this up?

UPDATE tableA
SET SKU = CONCAT("X-", tableA.supplier_SKU);

There is no index on any column yet.

EXPLAIN indicates rows=4.6 million, filtered = 100% !

CodePudding user response：

If there is an index(indexes) on SKU, dropping it, updating and recreating might help.

Can you lock the table first (ensure no other user is blocking your operation)?

lock tables tableA write;

Can you create another table, update there and then rename?

*note - link above describes how to swap two tables in one statement.

4.6M records doesn't sound like sth that should take hours, unless you can't lock the table because other users keep updating it.

CodePudding user response：

Please provide SHOW CREATE TABLE tableA.

The slow part is needing to save 4.6 million "old" rows before getting to the "commit".

Do not ever use LOCK TABLES with InnoDB.

You could break the task into chunks so that it blocks other actions less. (But the total time will probably be longer.) See this for 'chunking': http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks