Import Very Huge CSV file into MySQL

Question

I am having a huge file of comma separated values (CSV) that I need to import into a mysql database..

The file size is 7 GB, and the memory size on my machine is 4.00 GB..

I tried to load it using "LOAD DATA LOCAL INFILE" SQL Command, but it has been more than 12 hours and it is still running !

The ideas that I think might should be done is to either :

Create a program that reads the file line by line and insert them into the database row by row.
Break the CSV file into a number of other CSV files with smaller size (may be 200 Mb) and load each of them into the database using the "LOAD DATA LOCAL INFILE"

Do you think there might be a better and more efficient approach to import such huge file into the MySQL ?

no, i was wondering why a gui client would make things faster. i have no idea how long it should take. what is limiting the speed? are you out of memory, or waiting on the disk? or at 100% cpu? do you have any keys defined on the table? what is the schema? what operating system are you using? — andrew cooke
– andrew cooke, Commented May 22, 2013 at 9:55
The schema is pretty simple is it 34 columns with one of them defined as a primary key. The CPU usage is not so big it is just around 2-3 % The memory also doesn't seem to be growing that large as I imagined.. That what makes me feel like that the "LOAD Data Local Infile" is just stuck and not doing anything... While doing the insertion line by line I may add a progress indicator that shows roughly the number of rows inserted so far. (PS: I inserted the same data into SQLite database in less than 30 minutes only) — Moustafa Alzantot
– Moustafa Alzantot, Commented May 22, 2013 at 10:05
dropping the key usually makes adding large amounts of data faster. did you try it on a short version of the file first to be sure it works ok? how does time vary with the number of lines in the file? — andrew cooke
– andrew cooke, Commented May 22, 2013 at 10:07

andrew cooke · Accepted Answer · 2013-05-22 12:32:22Z

1

[So you can mark the Q as closed - and I get some points!]

Dropping the index should help.

(Although I am surprised it helped so much. I guess MySQL couldn't keep the whole index in memory, which seems odd, as it should be smaller than the data.)

answered May 22, 2013 at 12:32

andrew cooke

47.1k10 gold badges102 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Import Very Huge CSV file into MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related