Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Adding multiple indexes at same time in MySQL

Tags:

indexing

mysql

During tests on MySQL, I wanted to add multiple indexes to a table with more than 50 million rows. Does MySQL support adding 2 indexes at the same time for different columns? If yes, Do I need to open 2 sessions or it can be done from one command?

like image 508
mugurelf Avatar asked Jun 03 '15 17:06

mugurelf


2 Answers

Yes. But...

In older versions, use

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

so that it will do all the work in one pass.

In newer versions, ALGORITHM=INPLACE makes it so that the work can be done in the "background" for InnoDB tables, thereby having less impact on other processing. However, to get the INPLACE processing, you may need to add each index separately. [Check the manual for the specific syntax; there have been many changes in the 6+ years since I wrote this Answer. Also, MySQL and MariaDB are not in lock-step.]

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

like image 82
Rick James Avatar answered Sep 28 '22 04:09

Rick James


It is useful to create it in the time of the table creation, which can be done like this:

CREATE TABLE name_of_a_new_table (col1 variable_type_1, col2 variable_type_2,
       INDEX idx_col1 (col1), INDEX idx_col2 (col2));
like image 39
Armin Okić Avatar answered Sep 28 '22 04:09

Armin Okić