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

I need to insert rows as fast as possible into a database with freshly created tables and no indexes. I'm looking at on the order of 20M rows, which is not that much. Would InnoDB or MyISAM tables give me the best performance?

Obviously, there are other batch loading tricks that I'm looking at as well.

share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

InnoDB is transactional, so MyISAM, which doesn't worry about atomic writes, will be quicker.

share|improve this answer
1  
I ran a test which took 30 minutes with InnoDB, but only 6 with MyISAM! –  Josh Glover Feb 17 '11 at 12:47
add comment

Here is something no one has mentioned yet.

If you convert the row format of MyISAM tables from DYNAMIC (default) to FIXED, you can increase overall SQL performance against the table at least 20%.

According to pages 71-73 of MySQL Database Design and Tuning (ISBN 0-672-32756-1), just run the following command on any MyISAM table:

ALTER TABLE tblname ROW_FORMAT=FIXED;

and you will get a nice performance boost on your queries against that table.

The example given in the book stated that queries against DYNAMIC-row MyISAM table took 50% longer to execute that the FIXED-row MyISAM table. If you then take the FIXED-row MyISAM table and compress it using myisampack, the compressed FIXED-row MyISAM table queries 10% faster than the uncompressed FIXED-row MyISAM table.

There is a grave drawback to this approach ??? The FIXED-row MyISAM table are at least twice as large as its DYNAMIC-row MyISAM counterparts. So, it you could afford to tradeoff speed for space, you should go for FIXED-row MyISAM tables. Then, all queries will be faster without any additional table structure changes.

You could also run the ALTER TABLE tblname ROW_FORMAT=FIXED; against an InnoDB table as well. If you did this to tables in both InnoDB and MyISAM storage engines, you still would not have a level playing field. The edge would always go to MyISAM. The reason ??? All non-unique index lookups must traverse a nonclustered index and then through the clustered index for rowid access. MyISAM indexes only go through one index at most.

I am not just conjecturing on this. About 4 years ago, I personally took a 2GB table, ran the ALTER TABLE, and expanded it to 3.7GB. All resulting queries averaged 20% faster than it was running prior to making the row format change.

I highly recommend doing this for all MyISAM and InnoDBs if you want increased performance and if you do not mind increased file sizes.

share|improve this answer
    
Very cool! Thanks. –  Josh Glover Apr 22 '11 at 6:12
add comment

MyISAM will be faster than InnoDB for the case you describe. If you're simply looking to get data into MySQL as fast as possible, look at LOAD DATA INFILE.

share|improve this answer
    
Thanks for the tip on LOAD DATA INFILE. I think I can actually use it for my application. That combined with MyISAM should speed my process up quite a bit. –  Josh Glover Feb 17 '11 at 11:41
add comment

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.