Why does MySQL use a temporary table to drop a primary key?

Question

When using the command:

  ALTER TABLE my_table DROP PRIMARY KEY;

The state (when SHOW PROCESSLIST) appears as:

  copy to tmp table

Why would it need to use a tmp table to "drop" a primary key constraint?

Answer 1

Consider the case of a composite primary key. In this case, the DB engine has to create a new clustered index from a synthetic key, which will require moving rows around. (Keep in mind that rows are physically ordered on disk by the primary key.) Given the rarity of this situation, it's not really worth handling the special case where your primary key is already an integer.