ALTER TABLE ...ROW_FORMAT=Fixed. By itself this will speed up insert times enormously, but a side-effect will be that the table may double in size.
The other main points with MyISAM are disabling keys and setting session bulk_insert_buffer_size, myisam_sort_buffer_size and key_buffer_size; see http://venublog.com/2007/11/07/load-data-infile-performance.
InnoDB has to transactionally process each Insert. With huge tables, the process can slow to a crawl---so much so that some DBAs advocate simply avoiding InnoDB for huge reference tables.
Apart from that, with InnoDB the main points are disabling foreign key checks and setting innodb_additional_mem_pool_size, innodb_buffer_pool_size, innodb_log_buffer_size, and innodb_log_file_size; see
http://www.mysqlperformanceblog.com/2007/05/24/predicting-how-long-data-load-would-take/.
Last updated 16 Aug 2024
