HOW TO WORK WITH MASSIVE DATA FILES

[jwwceo]

I am developing a database app for a client who needs to import hundreds of thousands of codes into the DB to check against. The codes are in 4 text files about 30MB each.

 

The codes are 3 per line, then a line break, and 3 more. Ive written a script to parse out the line breaks, turn the data into an array, then loop over thay array and insert into the DB. The problem is these scripts take minutes to run using file_get_contents and by the time the data is ready the mmysql connection is gone. Plus even these files only work after Ive cut the files into about 1MB each, so each file is 30 smaller ones.

 

Is there a way to just put the text file on the server, and have php search it using a GREP like function that won't be such a burden to work with. Any advice helps.

 

James

 

[jwwceo]

UPDATE...there are about 18 million codes...they are all 4 characters...if this helps give the extent of the size issue.

[petroz]

If you can get it into a csv file, you can import the csv file right into mysql without php at all. Take a look at.

 

http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

[mikosiko]

LOAD DATA INFILE  will be my choice

 

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

 

table without indexes improve even more the load performance

[Kryptix]

Why not use PHP in command prompt and open up a new connection each time you're importing rather than just once?

[roopurt18]

petroz and mikosiko are proposing the correct solutions here.