MySQL’s LOAD DATA INFILE

In my Information Architecture class, we’re doing MySQL query optimization, and are required to import data from an Excel spreadsheet into the appropriate MySQL database schema. Many people are trying to use NaviCat to do the import of the data, but are having some problems.

I propose a better way to go about doing this is exporting the data as delimited text files, and importing the data with MySQL’s LOAD DATA INFILE.  I first came upon this mysql functionality as I was working on building an International geocoder. I was importing millions of records in tab-delimited format from the National Geospacial Intelligence Agency.

I first tried importing all of this data with a php script, doing a few million insert statements. This was very slow, as I could only get 100 or so rows to insert per second. I remember it would have taken something like 16 hours to get all of the records inserted into the database.  It just wasn’t feasible.

MySQL’s LOAD DATA INFILE command handles this stuff with ease. You just point it to your csv file, define the data a little bit, and let it go. I was able to import all of the data from the geospacial tab-delimited file in minutes rather than hours.