Optimise Load Data Infile
from the Artful MySQL Tips List
With MyISAM, a spectacular improvement in Load Data Infile performance can be got from
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
; 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
Return to the Artful MySQL Tips page