Adding a primary key to an existing table in mysql is taking time

22 hours ago I started a script that executes an alter table to add a primary key to a table which has 225 millions of records. MSISDN is the only column.

In showproceslist its process status is "copy to tmp table". Can anyone tell me if this is the correct behaviour and how long it will take to complete?

Yes, creating a PK on a big table takes time.

When you don't have PK (more precisely a clustered index - the two is the same in MySQL using InnoDB) the table is called heap and the DBMS adds a 'row identifier' to each row to track them. This ID is used to identify records in indexes and other places.

When you have a clustered index (PK in MySQL), the PK key will be the row's identifier and all indexes are using the clustered key to identify records.

When you modify the type of the table (heap to clustered), all indexes and the DBMS should rebuild the table and reorganize the data stored in it.

Best practices:

• Drop ALL nonclustered indexes before you change (or drop/create) the clustered key

• Stop all processes which are using that table to prevent deadlocks and long wainting (do it in your maintenance window)

• If you want to change (or drop/create) the clustered key on a huge table, create a new table with the required primary ky (clustered index), then copy all data from the original table, then replace the new table with the old by renaming. When this done, you can add additional indexes to the table.

how long it will take to complete?

It depends on the server and the processes runing next to the alter on the server. In short, we simply can not say a number how long it will take.

StackExchange Database Administrators Q#83653, answer score: 4