Fastest way to update column value

Question

I need to set all values of a given column foo to a constant varchar bar. Is there a faster way to do that than with the vanilla UPDATE mytable SET foo = 'bar'?

Facts:

• This is an Oracle MySQL 8.0.20 server (via SELECT @@VERSION)
• This table uses the InnoDB engine
• The column is not part of any index
• The column datatype is varchar(2000).
• I can't change the datatypes
• I have full access to the server and could manipulate the .idb files directly, if necessary.
• The largest table has ~1.7mi records and the regular update takes ~30mins
• Memory, CPU and storage doesn't seem to be a bottleneck, so far (checking iotop and htop itself)

Note: I need to do similar updates to multiple columns in multiple tables, but what I describe here applies to them all, except for the datatype, which is the worst case (all the other columns are also varchar, but not that long).

Answer 1

Changing 1.7M rows in a table requires making copies of all the rows that are changed. (This is in case there is a crash or ROLLBACK.) The system is not designed to handle huge changes like that.

It may be faster to do the UPDATE in chunks, COMMITting after each chunk. This way the undo stuff is not huge.

I discuss techniques for walking through a table via the PRIMARY KEY in http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks . That blog is worded in terms of DELETEs, but UPDATEs can use the same techniques.

Side notes. Some of these may lead to other solutions.

Do not even think of touching the .ibd file.

You do have a PRIMARY KEY, don't you? If not, that is bad. And it makes the "walking" that I describe less efficient.

Please provide SHOW CREATE TABLE and the actual UPDATE if you want to discuss things further.

In general, it is "bad schema design" to need to change every row of a large table. There are much more efficient techniques. One that comes to mind is to have a second table that contains only one row -- with the current value of foo. However, it may or may not work for your use case.

What is your use case? Do you change all the rows? Or does that UPDATE usually change only a few rows? How many different values? Is it a flag, a date, or what? Etc.

You have VARCHAR(2000); is the contents typically large? How big is the typical record? These questions lead into discussing "off-record" storage of large columns.

How much RAM do you have? What is the value of innodb_buffer_pool_size? What is the table size (SHOW TABLE STATUS LIKE 'mytable';)

Answer 2

I have full access to the server and could manipulate the .idb files directly ...

No, you really couldn't.

Database != File 

All you would achieve is completely breaking your database.

All DBMSs, not just MySQL, store their data files in proprietary, binary formats that mean absolutely nothing to the likes of you or me.

Let the database to worry about its own files and only ever make changes through the DBMS.