Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

Your intuition is correct AFAIK. Is the table ordered? http://kevin.vanzonneveld.net/techblog/article/improve_mysql_insert_performance/ Had some suggestions for optimizing bulk inserts.

share|improve this answer
    
Thanks for the pointer; some of those tips look quite promising! –  Josh Glover Feb 16 '11 at 16:58
    
I ended up going with LOAD DATA INFILE, which is substantially faster than any other loading tricks I've tried. –  Josh Glover Feb 23 '11 at 12:33

Here is something else to think about.

For all MyISAM tables, it is best to bulk load by disabling non-Unique indexes

ALTER TABLE myisamtabletoload DISABLE KEYS;
INSERT INTO myisamtabletoload ... ;
ALTER TABLE myisamtabletoload ENABLE KEYS;

Primary Keys and Unique Keys All Populated and Ordered During the INSERT.

Once ENABLE KEYS is executed, all non-Unique indexes are built via Sorting.

Unfortunately, DISABLE KEYS and ENABLE KEYS does not work for InnoDB because of the clustered key structures under its hood.

share|improve this answer
    
Good call. Luckily, I'm creating all indexes after loading the table, so I get to not worry about disabling the. :) –  Josh Glover Feb 23 '11 at 12:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.