CODEWITHSUNDEEP
mysqlperformance
user1604093
user1604093

Reputation: 89

MySQL change column name on EMPTY table, takes very long

Running a local Mysql instance. In my DB, I misspelled a column name (stret to street). So I wrote a query:

alter table address change Stret Street VARCHAR(20);

This table has just been created and contains zero records. I know there are various threads asking why it take so long but all of their tables has 100,000+ rows. I have nothing! Why did this query take 1 hour 13 min 15.76 sec?

I know I could have dropped and recreated the table, I am just curious as to why this "simple" change could take so long?

Edit: Found out the reason. I was debugging a program that uses this db and stopped in the middle (without terminating the program) to change the column name. Once I stopped tomcat everything is instant again. So presumably the table was locked so the query was held up. I am using InnoDB. Thanks everyone.

Upvotes: 4

Views: 1313

Answers (2)

Anirudh Ramanathan
Anirudh Ramanathan

Reputation: 46768

Quoting from the docs

In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL waits for other operations that are modifying the table, then proceeds. It incorporates the alteration into the copy, deletes the original table, and renames the new one.

If you are using InnoDB, it does the above even for single column renames, while with MyISAM, it does not create a copy and modifies table's frm file. That would explain the time taken probably, for a large table, due to memory constraints.

You could try obtaining a query execution-plan.

Upvotes: 2

tadman
tadman

Reputation: 211670

Before you do this you might want to TRUNCATE TABLE address (very fast but will reset your AUTO_INCREMENT column counters) or OPTIMIZE TABLE address (a bit slower, but doesn't alter data) in order to flush out any left-over data that is deleted but not vacuumed from the database.

An alternative is to CREATE TABLE _address LIKE address, run the alteration to the clone, then switch _address for address using RENAME TABLE.

Upvotes: 3

Related Questions