MySQL alter table memo

In most cases, when the alter table operation is performed, a temporary backup of the target table (temporarily named a) is copied (temporarily named B). The alter operation is performed on the backup file (B) and then delete the target table (a). Rename the backup table (B) as the target table (). When alter table is executed, other sessions can read the target table (a), but the update and write operations will be blocked until B is named, can be redirected to a without losing any DML operations. This temporary table B is created in the same database as the target table.

The following is the test process:

Alter table cdb_posts drop index FID;
Drop index FID on cdb_posts;
Alter table cdb_posts add index idx_fid (FID );
Create index idx_fid on cdb_posts (FID );

These operations are the same:

Copy to temporary table

Temporary table file created:

How to quickly create an index with a large amount of data, I have posted on the internet that, first create an empty table B, add the index field, and then insert .... select ....., then rename the old table, rename B to the target table, and then delete the old table. At first, I tested this in a million table, and the efficiency was worse than simply ALTER TABLE add index ..., alter table add index time: 9min19. 36sec: Cost: 11min17. 69sec

Reference: mysql-alter table usage