Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
Thanks for the answer. I'm aware of the work-around. –  Wolfram Arnold Mar 22 at 1:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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