mysql频繁insert优化,优化大型MySQL INSERT

I've got an application which needs to run a daily script; the daily script consists in downloading a CSV file with 1,000,000 rows, and inserting those rows into a table.

I host my application in Dreamhost. I created a while loop that goes through all the CSV's rows and performs an INSERT query for each one. The thing is that I get a "500 Internal Server Error". Even if I chop it out in 1000 files with 1000 rows each, I can't insert more than 40 or 50 thousand rows in the same loop.

Is there any way that I could optimize the input? I'm also considering going with a dedicated server; what do you think?

Thanks!

Pedro

解决方案

Most databases have an optimized bulk insertion process - MySQL's is the LOAD DATA FILE syntax.

To load a CSV file, use:

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name

FIELDS TERMINATED BY ',' ENCLOSED BY '"'

LINES TERMINATED BY '\r\n'

IGNORE 1 LINES;