2

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.

3 Answers 3

6

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

1
  • 2
    I ran a test which took 30 minutes with InnoDB, but only 6 with MyISAM! Feb 17, 2011 at 12:47
2

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.

1
  • 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. Feb 17, 2011 at 11:41
2

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.

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .