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.

WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put arbitrary user data directly into a query, it always requires escaping. — tadman
– tadman, Commented May 18, 2016 at 16:13
If you have well-formatted data, why not use LOAD DATA INFILE? — tadman
– tadman, Commented May 18, 2016 at 16:13
The load data operation might be a good idea but I just wanted to point out that using prepared statements goes significantly faster as the query is only analyzed once and then records can be inserted extremely fast. — Julie Pelletier
– Julie Pelletier, Commented May 18, 2016 at 16:25
+1 for Load data infile. Estimate: 2 to some seconds. You should try it, even if your file is not in the correct format, it might be faster to read the file into php and write a new, formatted file and use this for load data (it might be a good idea to do that anyway to check for errors or something). With prepared statements you might get to similar speeds though. — Solarflare
– Solarflare, Commented May 18, 2016 at 16:45

halfer · Accepted Answer · 2021-08-16 23:41:42Z

2

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);

edited Aug 16, 2021 at 23:41

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered May 19, 2016 at 2:19

Accountant م

7,6133 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

tadman Over a year ago

Good work on getting it running. Just remember that addslashes is not the right tool for this job and bind_param is. This difference might seem academic, but it's an important distinction because one is a very superficial treatment of the string that leaves you exposed to SQL injection problems, the other can handle any kind of data you throw at it. Parameterized queries solve a lot of nasty debugging problems, too, since your data and query are kept separated.

tadman Over a year ago

Also worth mentioning is that most MySQL servers run with default InnoDB settings. Not only can you increase the size of the buffer pool using innodb_buffer_pool_size but you can also use other performance tricks if it's necessary.

Accountant م Over a year ago

@tadman I used addslashes() because I couldn't add any slashes without it, I was getting the error can't find the file C:xampphtdocsbotsimpandexpimports.txt , why php removed all the slashes? is there any way else to add the slashes rather than addslashes() ?, thanks

tadman Over a year ago

Try doing LODA DATA IN LOCAL INFILE ? INTO TABLE ..., then use prepare, bind_param and execute. That leads to predictable, consistent results.

Collectives™ on Stack Overflow

how to optimize this sql insertion php code?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related