PHP imports a lot of data to MySQL performance optimization techniques _php tips

This article describes the PHP import of large amounts of data to MySQL performance optimization techniques. Share to everyone for your reference. The specific analysis is as follows:

In MySQL, we combine some files into MySQL, here to share my 15,000 records when the introduction analysis and optimization, the need for friends can refer to.

Before a few articles, said recently Tiandi in helping a friend to do a small project, used for statistical telephone number, every time according to the demand from the database randomly generated packaging phone number, and then keep people dozen these phone numbers to sell products (small despise this behavior). But friends ask for help, we also have to help Ah, yes. The procedure has been completed two weeks ago and the completion of the test. A few days ago, a friend called to say, the time to import phone numbers more and more long, sometimes 10,000 records will be more than half an hour to see if you can find a way to improve this speed.

I got a little bit of an idea. Database structure is very simple, you can think of two fields, a word Gencun phone number, another word Gencun category, categories are c,d,e, etc., respectively, representatives have dialed through this phone, not dialed through this phone, did not call this phone and so on, and the whole program logic is this.

Get a TXT file with a phone number in it.

Import txt file into MySQL via program

Import, the detection of TXT in the phone number and MySQL in the duplication, if not repeat, directly insert new records, if repeated, you need to judge the number of categories to be updated.

Because each txt in the phone number import, all need to do a comparison, so the program will certainly take some time, here we put aside this reason, because the title of this article is to optimize the write speed, then the program when will write records? The logic above shows that when the database is matched, the write database operation is not found when there is a record (and, of course, update is the only place to discuss inserts), then the above logic is translated into code, almost as follows:

Copy Code code as follows:

$array for TXT file explode out of the array, each one for a phone number, $str for the type
for ($i =0; $i <count ($array); $i + +)
{
$tmpstr = "'". $array [$i]. "', '". $str. "'";
$sql = "INSERT into". $usertable. " (Tel,type) VALUES (". $tmpstr.");
mysql_query ($sql);
}

The above code is completely correct, but inefficient, when the TXT file contains tens of thousands of phone numbers, there will be tens of thousands of times to insert the database operation, although each database write operation is very fast, but tens of thousands of cumulative down, this implementation time can not be ignored, Tiandi simple test to insert 150 million records, time consuming almost 5 minutes, if coupled with the logic of the previous process, and so on, then half an hour is really not less, so that can not, you must reduce the number of database library writes to the, so the above code changes to the following:

Copy Code code as follows:

$sql 2= "INSERT into". $usertable. " (Tel,type,updatetime) VALUES ";
for ($i =0; $i <count ($array); $i + +)
{
$tmpstr = "'". $array [$i]. "', '". $str. "'";
$sql 2. = "(". $tmpstr. "),";
}
$sql 2 = substr ($sql 2,0,-1); Remove the last comma
mysql_query ($sql 2);

In this way, the entire write operation only 1 times, greatly shortened the execution time, almost 10 seconds on the 15,000 records, OK, this article to the end, if you also have to write a large number of data to MySQL time-consuming problem, try this article optimization method.

I hope this article will help you with your PHP program design.