Jerielle Jerielle - 2 months ago 9
SQL Question

Which is faster to load CSV file or multiple INSERT commands in MySQL?

Just need some advice here. Because I have a table that has 45,000 rows. What I did is I export the table as a multiple INSERT command. But the estimated time is too long. Which is faster to load in the sql? A rows that is exported as CSV or rows that exported as a multiple INSERT command?

Answer

LOAD DATA INFILE is the fastest.

hello, so you are dumping table by your own program. if loading speed is important. please consider belows:

  • ensure that multiple INSERT INTO .. VALUES (...), (...)
  • Disable INDEX before loading, enable after loading. This is faster.
  • LOAD DATA INFILE is super faster than multiple INSERT but, has trade-off. maintance and handling escaping.

  • BTW, I thing mysqldump is better than others.

how long takes to load 45,000 rows?

Comments