MySQL What Happens to an Index if I Alter the Column it's on?

Question

I'm in the process of updating my AUTO_INCREMENT PRIMARY KEY ID columns from INT to BIGINT. I'm using MySQL version 5.0.82 with InnoDB tables. They look something like:

FactTable
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| ID    | int(11)     | NO   | PRI | NULL    | auto_increment |
| Path  | varchar(64) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

AttemptTable
+---------+----------+------+-----+---------+----------------+
| Field   | Type     | Null | Key | Default | Extra          |
+---------+----------+------+-----+---------+----------------+
| ID      | int(11)  | NO   | PRI | NULL    | auto_increment |
| TriedOn | datetime | NO   |     | NULL    |                |
| FactID  | int(11)  | NO   | MUL | NULL    |                |
+---------+----------+------+-----+---------+----------------+

Where AttemptTable has KEY FK1 (FactID) and CONSTRAINT FK1 FOREIGN KEY (FactID) REFERENCES FactTable (ID). So the course of my updates has been:

ALTER TABLE AttemptTable DROP FOREIGN KEY FK1,
MODIFY ID BIGINT(20) NOT NULL AUTO_INCREMENT,
MODIFY FactID BIGINT(20) NOT NULL;

ALTER TABLE FactTable MODIFY ID BIGINT(20) NOT NULL AUTO_INCREMENT;

ALTER TABLE AttemptTable ADD CONSTRAINT FK1 FOREIGN KEY (FactID)
REFERENCES FactTable (ID);

Now, I've noticed that when doing these modifications, the key FK1 is still present after the constraint is dropped, and I assume it's still applicable after the constraint is recreated. But my question is, does MySQL update this key to be applicable for BIGINTs, or does it maintain only INT capabilities?

I looked through the MySQL reference guide for ALTER TABLE, and I found mention that if the size of a column is decreased to less than an index's length then the index will be shortened, but I couldn't find anything indicating what would happen to an index if the underlying column grows in size. (I'd like to know if I should be dropping and recreating the indexes, too, or if MySQL will do what I'm hoping.)

Answer 1

Basically, it rebuilds the index when you change a column's data type.

Actually, it rebuilds all the indexes in that table.

Some types of ALTER TABLE statements in MySQL result in a table restructure:

1. Lock the table.
2. Create a new empty table with the new definition for columns.
3. Copy all the data, row by row, from the old table to the new table. This naturally fills indexes in the new table, just as you would if you were INSERTing new data.
4. Swap the names on the tables.
5. Drop the original table.
6. Unlock the table.

This is especially true when you change the primary key column in an InnoDB table, since every InnoDB table is stored as a clustered index for its primary key.

So if you change the data type of a column, that applies to the new table, and it fills the index as it copies rows from the original table to the new table.