how to optimize this sql insertion php code?

Question

I have mysql database and I want to insert about 40'000 rows into it from PHP code , but my code takes more than 15 minutes to insert the rows, is there any chances to optimize it? where is my problem(PHP code / database design) ?

here are the details:

- the row data are stored in a utf-8 txt file the values separated by "\t" tab character and every row sets in one line of the file, like this

string view:

"value1\tvalue2\tvalue3\value4\value5\r\nvalue1\tvalue2\tvalue3\value4\value5\r\nvalue1\tvalue2\tvalue3\value4\value5\r\nvalue1\tvalue2\tvalue3\value4\value5\r\n"

text reader view:

value1     value2     value3     value4     value5
value1     value2     value3     value4     value5
value1     value2     value3     value4     value5
value1     value2     value3     value4     value5

-the data base has 3 tables as this:

table1 countries fields(1) (NAME varchar -primarykey-)
table2 products fields(2) (HS varchar - primarykey-, NAME varchar) 
table3 imports fields (6) (product_hs varchar -foreignkey->products(HS),
counteryname varchar - foreignkey->countries (NAME),
year year,
units int,
weight int,
value int)        

- php code was like this

$conn = new mysqli($hn,$un,$pw,$db);
if($conn->connect_error) {die($conn->connect_error);}

$x = 0; // row counter
ini_set('max_execution_time', 3000);

while(!feof($filehandle)){
$x++;
echo $x . ":  ";
$fileline = fgets($filehandle);
$fields = explode("\t", $fileline);
$query = "INSERT INTO imports(product_hs,counteryname,year,units,weight,value) VALUES(" . "'" . $fields[0] ."','". $fields[1] . "','2014','". $fields[2] . "','" . $fields[3] . "','" . $fields[4] .  "');";
$result = $conn->query($query);
if(!$result) {
    echo $conn->error . "</br>";
}else{
    echo $result . "</br>";
}
};

first I thought it is an index problem that slows down the insertion , so I removed all the indexes from "imports" table , but it didn't go faster!! is the problem from the database design or from my php code?

also note that the browser is notifying "waiting for response from the server" for the first 5 minutes then most of the remaining time is notifying "transferring data from server", is this because the response html has more than 40'000 line for the row counter1:1 </br> 2:1 </br> .....(declared in the php code)?

please consider I'm very newbie, thanks.

Answer 1

Thank you very much tadman and Hanlet Escaño and Uueerdo and Julie Pelletier and Solarflare for helping me in the comments.

I did 3 different changes in my PHP code using the approaches you suggested in the comments, then I tested the results and here are the tests results.

The conclusion of the 3 tests: as tadman suggested, the key is in LOAD DATA INFILE . it dramatically reduced the execution time to less than 7 seconds, and these are the 3 tests.

---

ORIGINAL CODE: ~ 26 minutes

---

TEST 1 : ~ 34 minutes

(as Uueerdo suggested I removed the echo statements and the rows counter from the loop)

while(!feof($filehandle)){
// $x++; // commented out
//echo $x . ":  "; // commented out
$fileline = fgets($filehandle);
$fields = explode("\t", $fileline);
$query = "INSERT INTO products(hs,arabicname,englishname) VALUES(" . "'" . str_replace("'", ".", $fields[0]) ."'," . "'". str_replace("'", ".", $fields[1]) . "'," . "'". str_replace("'", ".", $fields[2]) . "');"; 
$result = $conn->query($query); 
/* // commented out
if(!$result) {echo  $conn->error . "</br>";}
}else{echo $result . "</br>";}
*/};

---

TEST 2 : ~ 7 seconds

(As tadman said I searched for LOAD DATA INFILE and it was super powerful

//replace the entire loop with this simple query
$query = "LOAD DATA LOCAL INFILE'" . 
addslashes("C:\\xampp\\htdocs\\bots\\impandexp\\imports.txt")
. "' INTO TABLE imports FIELDS TERMINATED BY '\t' LINES TERMINATED BY
'\r\n'(product_hs,counteryname,units,weight,value) SET  year = '2014';";
 $result = $conn->query($query);

---

TEST 3 : ~ 5 seconds

It was the same as test 2 except that I found useful tips on the same page that tadman give, that help in maximizing the speed for.

Bulk Data Loading for InnoDB Tables

// turning off index checks that might slows down bulk data insertion
$query = "SET foreign_key_checks=0;";
$conn->query($query);
$query = "SET unique_checks=0;";
$conn->query($query);
$query ="SET autocommit=0;";
$conn->query($query);

$query = "LOAD DATA LOCAL INFILE'" . addslashes("C:\\xampp\\htdocs\\bots\\impandexp\\imports.txt") . "' INTO TABLE imports FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'(product_hs,counteryname,units,weight,value) SET  year = '2014';";
$result = $conn->query($query);
echo $result . "</br>";
// turning them on again
$query = "SET foreign_key_checks=1;";
$conn->query($query);
$query = "SET unique_checks=1;";
$conn->query($query);
$query ="COMMIT;";
$conn->query($query);