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'?


  • 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).

  • What is your criteria of efficiency? What's wrong with a simple update?
    – mustaccio
    Jul 6, 2020 at 19:41
  • how many rows do you have. All solutions take time
    – nbk
    Jul 6, 2020 at 19:42
  • Updated the title and more facts. My focus is execution time. Sorry for not being clear in the first place.
    – tavlima
    Jul 6, 2020 at 19:59
  • Please post TEXT results from MySQL Command Prompt for query SELECT @@VERSION; so we know the specific version you are using. Jul 7, 2020 at 11:01
  • Comments addressed (cc @Akina @Wilson)
    – tavlima
    Jul 7, 2020 at 12:49

2 Answers 2


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';)

  • Thanks for the explanation, @Rick. You got a bunch of questions there, and I will try to address those as soon as possible, but, for now, I can tell you that this is not a regular process. I need to share this data with folks not privy to the content of some columns, thus this is like a "redaction" process. In fact, bar is, in most cases, just redacted. Any chance I could skip the logs and make things faster? I understand I may have issues in case of a crash.
    – tavlima
    Jul 8, 2020 at 15:59
  • @tavlima - Changing table/column names -- sure; losing datatypes -- not wise; "simplifying" the query -- may lead to solving the wrong problem. It is not possible to skip the undo/redo logs.
    – Rick James
    Jul 8, 2020 at 21:11

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.

  • Yeah, @Phill, I'm aware of that. I just brought that up because I have seen tools that would need that kind of access (like some Percona CLI tools, for instance).
    – tavlima
    Jul 8, 2020 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.