MySQL create multiple indexes in one sql 2017-11-17 10:35
The statement of creating index on table will lock the table.It will cost a long time if you create multiple indexes in a big table. You can use ALTER TABLE ... to create or drop multiple indexes in one sql. The example is here.
Every CREATE INDEX... statement will lock table.
-- two create index statement will lock table twice
CREATE UNIQUE INDEX users_user_name_uindex ON test_db.users (user_name);
CREATE UNIQUE INDEX users_address_uindex ON test_db.users (address);
Use ALTER TABLE... statement to create indexes will lock table only once.
ALTER TABLE test_db.users
ADD UNIQUE INDEX users_user_name_uindex(user_name),
ADD UNIQUE INDEX users_address_uindex(address);
The statement of drop multiple indexes
ALTER TABLE test_db.users
DROP INDEX users_user_name_uindex,
DROP INDEX users_address_uindex;
EOF