Newbie Issue With Super-Slow Load of Text file into MySQL
Posted: Mon Nov 03, 2008 2:46 pm
I'm attempting to automate the loading of an 80,000 record (31 fields) tab and space delimited syslog into MySQL using PHP. It works, but takes 40 minutes to run, so I'm obviously missing something basic and critical. Any suggestions on what I'm missing here is greatly appreciated.... (the array_pad is used because the latter half of the record, the space-delimited part, is frequently missing.)
$handle= @fopen($filename, "r");
if ($handle) {
while (!feof($handle)) {
$line = fgets($handle, 4096);
$myArray = preg_split('/[\s\t]+/', $line);
$dataArray = array_pad($myArray, 30, "NULL");
mysql_query('INSERT INTO `databasename`.`tablename` (`auto_id`, `field1`, `field2`, `field3`) VALUES (NULL, \'' . $dataArray[0] . '\', \'' . $dataArray[1] . '\', \'' . $dataArray[2] . '\')');
}
fclose($handle);
}
$handle= @fopen($filename, "r");
if ($handle) {
while (!feof($handle)) {
$line = fgets($handle, 4096);
$myArray = preg_split('/[\s\t]+/', $line);
$dataArray = array_pad($myArray, 30, "NULL");
mysql_query('INSERT INTO `databasename`.`tablename` (`auto_id`, `field1`, `field2`, `field3`) VALUES (NULL, \'' . $dataArray[0] . '\', \'' . $dataArray[1] . '\', \'' . $dataArray[2] . '\')');
}
fclose($handle);
}