Need to make MySQL's LOAD DATA LOCAL INFILE load large data faster
I'll bet that you are currently I/O bound. This means that nothing can speed it up. (And Rolando's suggestions may be futile.)
Let's look deeper. Is this LOAD a recurring task? If so, how often? Is everything blocked waiting for table to be reloaded? Simple solution: Load into a different table, then do a double RENAME TABLE
to swap it in. Only milliseconds of downtime.
Is the data coming from another machine? Use the network for the "input" side of the LOAD
rather than having the one disk fighting for reads versus writes.
Do you have a lot of indexes? There are several directions to take this question. Let's see SHOW CREATE TABLE
before barking up these tree(s).
Does the entire load need to be a single transaction? Multiple transactions may be faster because of not overflowing the log file. (I've seen 2x.)