What's the complexity of renaming a column in MySql?

Question

We want to rename a column in our MySql (version 5.5) database with something like this:

ALTER TABLE t1 CHANGE a b INTEGER;

Is this an O(N) (N=number of rows in the table) or an O(1) operation? I can't seem to find an answer in the manual nor on Google. The table has millions of records, and it wouldn't be feasible directly if it's O(N).

Answer 1

Unfortunately, it is O(N). Internally MySQL creates a new table with the desired structure, copies all your data over to that, and then finally swaps datafiles to effect a drop of the original table with a "rename" of the new. You can find this in MySQL's manual -- it's buried (hence why I can't find the direct link for you right this moment), but it's there.

A possibility for you to consider that I've used when restructuring large tables is essentially to take these same steps yourself:

1. Create new_table with the desired structure
2. Run INSERT INTO new_table (SELECT * FROM old_table) -- note that you'll need to in fact be explicit with columns at least for the INSERT, and I recommend for the SELECT as well (that way you'll have a pattern you can follow when re-ordering columns as well).
3. Drop the original and rename the new

The advantage to doing it yourself is that you can do the schema change and the initial population while your database is live, and then lock the original before you do a final update for any data that may have changed in the meantime and then the final swap-it-into-place. (Note that this assumes you have some mechanism -- maybe a TIMESTAMP field -- to identify rows that change in the meantime.)

• Wendy Adi: sorry maybe out of topic @kromey is it O(N) for postgresql too?

Answer 2

It can be an O(1) operation if only the name is changing, see:

https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

and search for:

Renaming, Redefining, and Reordering Columns

That is in a section that explains that the operation only modifies table metadata.

Back in MySQL 5.5 the manual says it works there too, except for InnoDB tables.

• ypercubeᵀᴹ: @SimonNutali please add in your answer that it (online, metadata only change) applies only to version 8.0 (and I think 5.6) but not for 5.5, that the question was about.