Millions data insertion speed test in MySQL

100W of data for many friends, but today I thought of creating a large number of data test environment, so I looked for how to insert 100W data, I used 20 fields. In contrast, the first is to use the MySQL stored procedures to get:

The code is as follows

Copy Code

Mysql>delimiter $ Mysql>set autocommit = 0$$ Mysql> CREATE PROCEDURE Test () Begin Declare i decimal (a) default 0; Dd:loop INSERT into ' million ' (' categ_id ', ' categ_fid ', ' sortpath ', ' address ', ' p_identifier ', ' pro_specification ', ' name ', ' Add _date ', ' picture_url ', ' thumb_url ', ' is_display_front ', ' create_html_time ', ' hit ', ' Buy_sum ', ' athor ', ' Templete _style ', ' is_hot ', ' is_new ', ' is_best ') VALUES (268, 2, ' 0,262,268, ', 0, ' 2342 ', ' 423423 ', ' 123123 ', ' 2012-01-09 09:55:43 ', ' upload/product/20111205153432_53211.jpg ', ' Upload/product/thumb_20111205153432_53211.jpg ', 1, 0, 0, 0, ' admin ', ' 0 ', 0, 0, 0; Commit Set i = i+1; If i= 1000000 then leave DD; End If; End Loop DD; end;$ Mysql>delimiter; Mysql> call test;

So we looked at it for 58 minutes and it was too jet lag.

Mysql> call test;
Query OK, 0 rows affected (min 30.83 sec)
Very time-consuming.

So I found another way.

First generate data using PHP code, and then import:

The code is as follows

Copy Code

<?php $t =mktime (); Set_time_limit (1000); $myFile = "E:/insert.sql"; $fhandler =fopen ($myFile, ' WB '); if ($fhandler) { $sql = "268t2t ' 0,262,268, ' t0t ' 2342 ' t ' 423423 ' t ' 123123 ' t ' 23423423 '" T ' 2012-01-09 09:55:43 ' t ' upload/product/ 20111205153432_53211.jpg ' t ' upload/product/thumb_20111205153432_53211.jpg ' tnulltnullt38t ' pieces ' t ' t123t123t0 '; $i = 0; while ($i <1000000)//1,000,000 { $i + +; Fwrite ($fhandler, $sql. " RN "); } echo "Write successfully, time-consuming:", mktime ()-$t; }

and then import

The code is as follows	Copy Code
LOAD DATA local INFILE ' e:/insert.sql ' to TABLE tenmillion (' categ_id ', ' categ_fid ', ' sortpath ', ' address ', ' p_identifie ') R ', ' Pro_specification ', ' name ', ' description ', ' add_date ', ' picture_url ', ' thumb_url ', ' shop_url ', ' shop_thumb_url ', ' brand_id ', ' unit ', ' square_meters_unit ', ' market_price ', ' true_price ', ' square_meters_price ';

The result is less than 1 minutes, and 1 million of the data is quickly imported,

Note that the fields are no longer separated by commas, separated by T, and records are segmented in Rn. As a result, I inserted 10 times, and the 100W averaged 1 minutes.

Summary, in the large amount of data processing we would be better to use a third-party plug-ins to implement a data backup or directly on the server for backup, with MySQL with the tools are sometimes really not ideal.