0

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

  • What is your criteria of efficiency? What's wrong with a simple update? – mustaccio Jul 6 at 19:41
  • how many rows do you have. All solutions take time – nbk Jul 6 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 at 19:59
  • Please post TEXT results from MySQL Command Prompt for query SELECT @@VERSION; so we know the specific version you are using. – Wilson Hauck Jul 7 at 11:01
  • Comments addressed (cc @Akina @Wilson) – tavlima Jul 7 at 12:49
0

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

| improve this answer | |
  • 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 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 at 21:11
0

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.

| improve this answer | |
  • 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 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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