What is the best way of uploading 1,600,000 rows of data into mysql database ?

Question

This might sound dumb but yeah i need a effective way of doing this. Am using php at the moment and man it is slow.

Any pointers please.

Cheers.

Answer 1

Anytime I have to insert massive amounts of data, I try to use "LOAD DATA INFILE":

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

The bottleneck could also be the indexing of the data. I would recommend disabling indexing prior to running the load data infile and then enabling it afterward.

Answer 2

I like HediSQL http://www.heidisql.com/. I have moved databases with over a million rows using it. There is an import an export feature that works quite well. It's a great mysql client and makes working with mysql much easier than using phpMyAdmin.

Answer 3

One thing to try is deleting any indexes before you do the insert. Once you have inserted everything create the indexes again.

MySql actually has a really good article on this and other methods. Take a look at it here.