Is it faster to create indexes before or after data loading in MySQL?

Posted by Josh Glover on Server Fault See other posts from Server Fault or by Josh Glover
Published on 2011-02-16T16:24:42Z Indexed on 2011/02/18 15:27 UTC
Read the original article Hit count: 263

Filed under:

I have a data replication process that drops and recreates a few tables in a target database, then loads them up with data from a source database (running on another host, but that is immaterial to the question at hand).

The target database does need primary keys and a few other indexes on its tables, but not during the data loading. I'm currently loading all of the data, then creating the indexes. However, index creation takes a pretty long time--30 minutes of my data loader's 5 and a half hour running time.

My intuition tells me that creating the indexes at the end should be faster than creating them first, since the index would need to be rewritten with each insert.

Can anyone tell me for sure which way is faster? FWIW, I'm running MySQL 5.1 with InnoDB tables.

© Server Fault or respective owner

Related posts about mysql