MySQL: Increase the Performance of CREATE INDEX and DROP INDEX for InnoDB

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In MySQL 5.5 and higher version, Index performance has improved a lot.
MySQL InnoDB engine has basically two types of index, Primary clustered index and Secondary indexes.

When we are adding or dropping a clustered index, It is copying the data, and creating a new copy of the table.
Yes, this is the basic nature of any clustered index because It also contains the data value.

But, what about Secondary Index of MySQL InnoDB ?

When we are adding or dropping a secondary index, internally It scans the table, and sorts the rows using memory buffers in order by the values of the secondary index key columns.

Once a CREATE INDEX or ALTER TABLE statement starts to create InnoDB secondary index, we can only read the table, but cannot update the table.

Now Imagine, that we have a table with billions of data and now we require to add few more secondary indexes.
I have noticed that, most of the database developers create individual CREATE INDEX statement and execute each for creating require indexes.

We should not do this way because every CREATE INDEX statement require clustered index scan and other sorting related operation which actually degrade the performance of CREATE or DROP secondary index.

If we are adding or dropping multiple Indexes at a time, We can increase the performance of CREATE INDEX and DROP INDEX by executing using one single ALTER TABLE command.

If we are dealing with bulk-insert operation, we should create require indexes after data insertion which increase the performance of bulk-insertion.

For you guys, small demonstration to add or drop multiple indexes using one ALTER COMMAND.

Create a sample table:

Insert few sample records:

One ALTER command to add multiple Indexes on Table:

One ALTER command to drop multiple Indexes on Table: