Read a csv file and insert into DB table 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.
$db = new PDO(&quot;mysql:host=$db_host;port=$db_port;charset=utf8&quot;,&quot;$db_username&quot;, &quot;$db_password&quot;);
$csv_path = &quot;path/to/myfile.csv&quot;;
$table = &quot;TableName&quot;;
//Start of the query
$query=&quot;USE &quot;.$db_name.&quot;;&quot;;


$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, &quot;r&quot;);

while (($data = fgetcsv($handle, 2000, &quot;;&quot;  ,  chr(8))) !== FALSE) {

        $number_of_data = count($data)-1;


        // Generate MySQL query
        if($is_not_title_row)
        {
            $query .= &quot;INSERT INTO &quot;.$table.&quot; VALUES (&quot;;

            //We read all the data of one row
            for ($c=0; $c &lt;= $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(&quot;Y-m-d&quot;, strtotime($data[$c]));
                    $query .= &quot;'&quot; . mres($data[$c]) .&quot;',&quot;;
                //If there is nothing, we send NULL value
                else if(mres($data[$c])==NULL){
                    $query .= &quot;NULL,&quot;;
                }
                else{
                        $query .= &quot;'&quot; . mres($data[$c]) .&quot;',&quot;;
                }


                //If this is the end of the INSERT INTO we remove the comma
                if($c==$number_of_data)
                {
                    $query=substr_replace($query ,&quot;&quot;, -1);
                }

            }
            $query .= &quot;);&quot;;


            $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 &quot;There is &quot;.$row_batch_count.&quot; row sent&lt;br/&gt;&quot;;
                echo &quot;&lt;br/&gt;&lt;br/&gt;&quot;;
                $query_to_execute = $db-&gt;prepare($query);
                $query_to_execute-&gt;execute();


                $query=&quot;USE &quot;.$db_name.&quot;;&quot;;

                $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-&gt;prepare($query);
        $query_to_execute-&gt;execute();
        $query_to_execute = NULL;

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
What I found to greatly improves the speed of insert queries, is inserting multiple rows using a single query. You seems to have found the same solution, but it is implemented in a very roundabout way.
Your code could also benefit a lot from using functions. So, let's start with the most important one: How to insert multiple rows. It could look something like this:
function insertRows($db, $table, $rows)
{
    $values    = '(' . implode(',', array_fill(0, count($rows[0]), '?')) . ')';
    $query     = 'INSERT INTO '.$table . ' VALUES ' .
                 implode(',', array_fill(0, count($rows), $values));
    $statement = $db-&gt;prepare($query);
    $index     = 1;
    foreach($rows as $row) {
        foreach($row as $value) {
            $statement-&gt;bindValue($index++, $value);
        }    
    }
    $statement-&gt;execute();
}

This bit of code has only one function: Insert a certain number of rows into the database as quickly as possible. This function doesn't read data, change it in any way, but it does use parameter binding for extra security. That's probably not needed in this case, but it also doesn't hurt. No more problems with quotes.
You can insert quite a few rows using this function, somewhere around 50 to 150 rows, depending on what your input looks like. Above a certain number the speed simply won't increase anymore, so there's no point in going to extremely large number of rows.
I would create another function for changing the row data from the format you received to the format you want to insert:
function reformatRow($row)
{
    // &lt;...put your code here...&gt;
    return $row;
}

We can now also implement a function that does the above for multiple rows:
function reformatRows($rows)
{
    foreach ($rows as $key =&gt; $row) {
        $rows[$key] = reformatRow($row);
    }
    return $rows;
}

Functions don't have to be complex to be useful. I actually will not use this function, but I thought it was nice to show it.
All that's now left to do is to read the input, so let's do that. I will use parts of your code:
const MAX_ROWS = 100;

fgetcsv($handle, 2000, &quot;;&quot;, chr(8)); // read header and dump it

$rows = [];
while (($row = fgetcsv($handle, 2000, &quot;;&quot;, chr(8))) !== FALSE) {
    $rows[] = reformatRow($row);
    if (count($rows) &gt;= MAX_ROWS) {
        insertRows($db, $table[$i], $rows);
        $rows = [];
    }
}

if (count($rows) &gt; 0) {
    insertRows($db, $table[$i], $rows);
}

I haven't fully tested all this code.
I also don't think this code will be any quicker than yours. There's also no way for me to test any optimization, since I don't have your data, so I'm afraid I can't help you there. Reading data into a database just takes time. It is possible that fgetcsv() is not the optimal way to read a big CSV file, but I haven't tested that.
What I have tried to make clear is that by splitting your code into functional parts it become easier to understand, and more reusable. The insertRows() is, for instance, is, partly, reused from my own code. Once you've written a function like that, and it works, you can reuse it everywhere.