It is possible to create multiple indexes on a table with one ALTER TABLE command. This is relatively efficient, because the clustered index of the table needs to be scanned only once (although the data is sorted separately for each new index). For example:

CREATE TABLE T1(A INT PRIMARY KEY, B INT, C CHAR(1)) ENGINE=InnoDB;
INSERT INTO T1 VALUES
(1,2,'a'), (2,3,'b'), (3,2,'c'), (4,3,'d'), (5,2,'e');
COMMIT;
ALTER TABLE T1 ADD INDEX (B), ADD UNIQUE INDEX (C);

The above commands create table T1 with the clustered index (primary key) on column A, insert several rows, and then build two new indexes on columns B and C. If there were many rows inserted into T1 before the ALTER TABLE command, this approach would be much more efficient than creating the table with all its indexes before loading the data.

You may also create the indexes one at a time, but then the clustered index of the table is scanned (as well as sorted) once for each CREATE INDEX command. Thus, the following commands are not as efficient as the ALTER TABLE command above, even though neither requires recreating the clustered index for table T1.

CREATE INDEX B ON T1 (B);
CREATE UNIQUE INDEX C ON T1 (C);

Dropping indexes in the InnoDB storage engine does not require any copying of table data. Thus, you can equally quickly drop multiple indexes with a single ALTER TABLE command or multiple DROP INDEX commands:

ALTER TABLE T1 DROP INDEX B, DROP INDEX C;

or

DROP INDEX B ON T1;
DROP INDEX C ON T1;

Restructuring the clustered index in InnoDB always requires copying the data in the table. For example, if you create a table without a primary key, InnoDB chooses one for you, which may be the first UNIQUE key defined on NOT NULL columns, or a system-generated key. Defining a PRIMARY KEY later causes the data to be copied, as in the following example:

CREATE TABLE T2 (A INT, B INT) ENGINE=InnoDB;
INSERT INTO T2 VALUES (NULL, 1);
ALTER TABLE T2 ADD PRIMARY KEY (B);

Note that when you create a UNIQUE or PRIMARY KEY index, InnoDB must do some extra work. For UNIQUE indexes, InnoDB checks that the table contains no duplicate values for the key. For a PRIMARY KEY index, InnoDB also checks that none of the PRIMARY KEY columns contains a NULL. It is best to define the primary key when you create a table, so you need not rebuild the table later.