Primary key addition for a table which contatins no data is too slow We are using mysql. We are running 10 alter queries which contains 3 Primary key deletion and 4 column addition and 3 primary key addition. And this alter query is on three tables which don't contain any data. To run this query system took 15s. Usually system wont take this much for alter query. But for a table which contains no data I am not sure why it is taking this much of time.

I am not sure whether it is because of primary key addition and deletion or any mysql configuration is wrong or because of the machine.

While this migration alone system is slow. After and before the migration is system is behaving as normal.

Can anyone help me on this one?

Below are the queries. I was running three sets like this. 

ALTER TABLE Table1 DROP PRIMARY KEY; 

ALTER TABLE Table1 ADD Column3 INT(10) DEFAULT ‘1'; 

ALTER TABLE Table1 ADD PRIMARY KEY Table_PK(Column1, Column2 , Column3 );
You are running in Windows, correct?  Perhaps an older version of Windows and an older version of MySQL?  File operations are slow.  ALTER TABLE, especially when changing the PRIMARY KEY, requires creating 1-3 files, copying over some stuff (not much in your case), removing the old 1-3 files, then renaming the files.

ALTER on an empty table is not a priority.  However, some improvements were made in 5.6 on Windows.  Still, I suggest that Windows file management is the main culprit. Your code may have added up to 27 system calls for file manipulation.  15 seconds seems about right for Windows.

You could speed the process up by combining the statements:

ALTER TABLE Table1
    DROP PRIMARY KEY,
    ADD Column3 INT(10) DEFAULT '1',    -- (I fixed the syntax error here)
    ADD PRIMARY KEY Table_PK(Column1, Column2 , Column3 );


With few exceptions, you can combine multiple ALTERs for a single table into a single ALTER.  And, unless all can be run with ALGORITHM=INPLACE, it is faster to combine them.

Consider using INT UNSIGNED for Column3.  And/or some smaller type of INT, such as SMALLINT.