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

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

Any pointers please.

Cheers.

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.

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.

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.