GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

CREATE INDEX performance on InnoDB

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • CREATE INDEX performance on InnoDB

    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

    Comment


    • #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.

      Comment

      Working...
      X