CREATE INDEX performance on InnoDB

MySQL & MariaDB Other MySQL® Questions
1

Hello there,

Could you give us some tips on how to improve CREATE INDEX performance on InnoDB?

Some secondary questions that might help to answer question above:

  • Is it faster to define indexes in the CREATE TABLE before loading data OR declare one ALTER TABLE per table with indexes after the load? or …
  • Are there innodb parameters that affect CREATE INDEX speed?

Thanks.

2

http://www.mysqlperformanceblog.com/2008/04/23/testing-innod b-barracuda-format-with-compression/

make sure you use a recent version

3

It depends on InnoDB version.

In older versions, it is faster to just load the data into the table. Adding the indexes rebuilds the entire table via something like insert/select, so you might as well just do it once by inserting the data and building the indexes the first time.

In Plugin versions, it is much faster to insert the data and then add the indexes, because of its ability to add indexes online.

The parameters have a lot to do with speed. As soon as the index doesn’t fit in the buffer pool, performance drops very sharply.