Clever Geek Handbook

Adding multiple indexes at the same time in MySQL

During my tests in MySQL, I wanted to add multiple indexes to a table with over 50 million rows. Does MySQL support adding two indexes at the same time on different columns? If so, do I need to open 2 sessions, or can this be done from one command?

+3


source to share


2 answers


Yes. But...

In older versions use

ALTER TABLE tbl
    ADD INDEX(...),
    ADD INDEX(...);


so that he does all the work in one pass.

In newer versions, ALGORITHM=INPLACE

makes it so that work can be done in the background for InnoDB tables, thereby having less impact on other processing. However, INPLACE

you may need to add each index separately to get processed .

The Ref manual lists some caveats, such as dealing with PRIMARY KEY

.

+6


source


Have you checked out the MySQL manual? InnoDB supports fast index creation, and maybe this is what you are looking for:



https://dev.mysql.com/doc/refman/5.5/en/innodb-create-index.html

0


source






More articles:


All Articles