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?
2
-
But the estimated time is too long. - how did you get this estimation?Viacheslav Kondratiuk– Viacheslav Kondratiuk2013-11-26 06:23:30 +00:00Commented Nov 26, 2013 at 6:23
-
I run this in a SQL browser softwareJerielle– Jerielle2013-11-26 06:25:28 +00:00Commented Nov 26, 2013 at 6:25
Add a comment
|
3 Answers
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 INFILEis super faster than multiple INSERT but, has trade-off. maintance and handling escaping.BTW, I thing
mysqldumpis better than others.
how long takes to load 45,000 rows?
2 Comments
Jerielle
Ok sorry for my late reply. As of now it takes 35 mins. But it is not done yet.
Jason Heo
@Jerielle thanks. as you know, load time depends your schema and data. but it's too late (2.15 rows per sec?). are you loading while there is another process? have you checked
SHOW PROCESSLIST while loading?INSERT is faster, because you are skip parsing of csv.
If your table at MyISAM, you can copy files: *.frm, *.myi, *.myd and this migration will be faster.
answered Nov 26, 2013 at 6:22
Viacheslav Kondratiuk
8,9609 gold badges53 silver badges81 bronze badges
Comments
In my experience, loading a csv is almost always faster with large datasets; it is built for mass loading.
See here for more info https://dba.stackexchange.com/questions/16809/why-is-load-data-infile-faster-than-normal-insert-statements
