Return to Question

Solution

Instead of doing one INSERT INTO for each row, I've simply inserted multiple row in a single INSERT INTO. The output query looks like that :

INSERT INTO Table VALUES (x,x,x),(x,x,x),(x,x,x),(...

The csv insertion is now 30 times faster 🚀

$db = new PDO("mysql:host=$db_host;port=$db_port;charset=utf8","$db_username", "$db_password");
//Start of the query
$query="USE ".$db_name.";";
$FilePath = $csv_path[$i];
$is_not_title_row=FALSE;//Usefull not to take the first row
$row_batch_count=0;//Counter for batches
//1 data = 1 part of the row
$number_of_data=0;
$data='';
// Open file in read-only mode
$handle = fopen($FilePath, "r");
while (($data = fgetcsv($handle, 2000, ";" , chr(8))) !== FALSE) {
 $number_of_data = count($data)-1;
 // Generate MySQL query
 if($is_not_title_row)
 {
 $query .= "INSERT INTO ".$table[$i]." VALUES (";
 //We read all the data of one row
 for ($c=0; $c <= $number_of_data; $c++) {
 $data[$c]=utf8_decode($data[$c]);
 $data[$c]=str_replace('/', '-', $data[$c]);
 $data[$c]=str_replace(',', '.', $data[$c]);
 
 //If it's a date, we convert it to the right date format
 if (DateTime::createFromFormat('d-m-Y', $data[$c]) !== FALSE) {
 $data[$c] = date("Y-m-d", strtotime($data[$c]));
 $query .= "'" . mres($data[$c]) ."',";
 //If there is nothing, we send NULL value
 else if(mres($data[$c])==NULL){
 $query .= "NULL,";
 }
 else{
 $query .= "'" . mres($data[$c]) ."',";
 }
 //If this is the end of the INSERT INTO we remove the comma
 if($c==$number_of_data)
 {
 $query=substr_replace($query ,"", -1);
 }
 }
 $query .= ");";
 $row_batch_count++;
 //If we are at the end of the batch, we send the query then start the creation of another
 if($row_batch_count==$batch_size)
 {
 echo "There is ".$row_batch_count." row sent<br/>";
 echo "<br/><br/>";
 $query_to_execute = $db->prepare($query);
 $query_to_execute->execute();
 $query="USE ".$db_name.";";
 $row_batch_count=0;
 }
 }
 //Usefull to remove the first row
 if(!$is_not_title_row){
 $is_not_title_row=TRUE;
 }
 }
 //Without more batches, we execute the query(=we are at the end of the file)
 $query_to_execute = $db->prepare($query);
 $query_to_execute->execute();
 $query_to_execute = NULL;

$db = new PDO("mysql:host=$db_host;port=$db_port;charset=utf8","$db_username", "$db_password");
$csv_path = "path/to/myfile.csv";
$table = "TableName";
//Start of the query
$query="USE ".$db_name.";";
$FilePath = $csv_path;
$is_not_title_row=FALSE;//Usefull not to take the first row
$row_batch_count=0;//Counter for batches
//1 data = 1 part of the row
$number_of_data=0;
$data='';
// Open file in read-only mode
$handle = fopen($FilePath, "r");
while (($data = fgetcsv($handle, 2000, ";" , chr(8))) !== FALSE) {
 $number_of_data = count($data)-1;
 // Generate MySQL query
 if($is_not_title_row)
 {
 $query .= "INSERT INTO ".$table." VALUES (";
 //We read all the data of one row
 for ($c=0; $c <= $number_of_data; $c++) {
 $data[$c]=utf8_decode($data[$c]);
 $data[$c]=str_replace('/', '-', $data[$c]);
 $data[$c]=str_replace(',', '.', $data[$c]);
 
 //If it's a date, we convert it to the right date format
 if (DateTime::createFromFormat('d-m-Y', $data[$c]) !== FALSE) {
 $data[$c] = date("Y-m-d", strtotime($data[$c]));
 $query .= "'" . mres($data[$c]) ."',";
 //If there is nothing, we send NULL value
 else if(mres($data[$c])==NULL){
 $query .= "NULL,";
 }
 else{
 $query .= "'" . mres($data[$c]) ."',";
 }
 //If this is the end of the INSERT INTO we remove the comma
 if($c==$number_of_data)
 {
 $query=substr_replace($query ,"", -1);
 }
 }
 $query .= ");";
 $row_batch_count++;
 //If we are at the end of the batch, we send the query then start the creation of another
 if($row_batch_count==$batch_size)
 {
 echo "There is ".$row_batch_count." row sent<br/>";
 echo "<br/><br/>";
 $query_to_execute = $db->prepare($query);
 $query_to_execute->execute();
 $query="USE ".$db_name.";";
 $row_batch_count=0;
 }
 }
 //Usefull to remove the first row
 if(!$is_not_title_row){
 $is_not_title_row=TRUE;
 }
 }
 //Without more batches, we execute the query(=we are at the end of the file)
 $query_to_execute = $db->prepare($query);
 $query_to_execute->execute();
 $query_to_execute = NULL;

I've found some resources about PHP performance optimization here and there but I don't know how to start. I send 130k CSV rows in approximatively 7min. My script read CSV file and send $batch_size row per query, while doing some string operations such as changing date to the correct format or replacing , with . for the floats.

I've found some resources about PHP performance optimization here and there but I don't know how to start. I send 130k CSV rows in approximatively 7min. My script read CSV file and send $batch_size row per query, while doing some string operations such as changing date to the correct format or replacing , with . for the floats.