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?
Add a comment
|
3 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 [ref manual] Ref manual lists some caveats, such as dealing with PRIMARY KEY.
5 Comments
mugurelf
Good to know. An additional info i want to know is if we want to modify a column to become primary key auto_increment , does the following command assure us that the auto_increment starts at the first record: ALTER TABLE table1 modify column content_id bigint(20) AUTO_INCREMENT primary key; ?
Rick James
Adding a PRIMARY KEY requires rewriting the table (the slow method). Yes, you can (should, for speed) do everything in a single
ALTER: ALTER TABLE tbl MODIFY ... ADD PRIMARY KEY (...), ADD INDEX ..., etc;Rick James
If
content_id already has data in it, are you adding AUTO_INCREMENT and PRIMARY KEY? Is it already BIGINT? (Please show us SHOW CREATE TABLE)mugurelf
CREATE TABLE
table1 ( content_id bigint(20) unsigned NOT NULL , pid int(11) unsigned NOT NULL, status int(11) unsigned NOT NULL DEFAULT '0', datecreated datetime DEFAULT NULL, datemodified datetime DEFAULT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8;Rick James
How big is the table? What is
MAX(content_id)? What is your tentative ALTER, based on the advice so far?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));
Comments
Have you checked the MySQL manual? InnoDB supports fast index creation, and maybe it is what you are looking for:
https://dev.mysql.com/doc/refman/5.5/en/innodb-create-index.html
