PHP imports a lot of data to MySQL performance optimization

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. The database structure is very simple, you can think of two fields, one word Gencun phone number, another word Gencun category, the category is C,d,e, etc., respectively, representatives have dialed through this phone, not dialed through this phone, not dialed 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 a database is matched, a write to the database operation is not found when there is a record (of course, update is, but only inserts are discussed here). Then turn this logic into code, almost as follows:

code is as follows	copy code
//$array for TXT file E Xplode out array, each for a phone number, $str for the type for ($i =0; $i <count ($array); $i + +)     {   & nbsp;     $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 simply tested the insertion of 150 million records, which took almost 5 minutes. If you add the logic of the previous process, and so on, then half an hour is really not less. This will not work, you must reduce the number of database library writes, so the above code changes to the following:

The code is as follows	Copy Code
$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);

This way, the entire write operation is only 1 times, greatly shortening the execution time and 15,000 records in almost 10 seconds. Well, this concludes this article, if you also encounter a large number of data written to MySQL time-consuming problem, it is better to try this article optimization method.