•  

Announcement

Announcement Module
Collapse
No announcement yet.

CREATE INDEX performance on InnoDB

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

  • CREATE INDEX performance on InnoDB

    #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.
    Tags: None
  • #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