Geek asks and answers

250k quick row insertion

So, I am trying to insert 250,000 rows into my database. Since these are just data samples, I just need to request the x, y variable, which changes, therefore, for loops and just the landscape, which is just “water”

But this happens forever. Does anyone know how I can do this faster?

ini_set('max_execution_time', 70000);

$conn = mysql_connect('localhost', 'root', '') or die(mysql_error());
mysql_select_db('hol', $conn) or die(mysql_error());

for ($y=0;$y<500;$y++) {
    for ($x=0;$x<500;$x++) {
        mysql_query("INSERT INTO map(x, y, terrain) VALUES($x, $y, 'water')");
    }
}
+5
source share
4 answers

You can better use the MySQL LOAD DATA FROM INFILE statement. Batch loading tends to be faster than building massive lines of insert statements. Write your data to a file and do one DOWNLOAD.

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

/ . . , , , .

+8
INSERT INTO map (x, y, terrain) 
 VALUES ($x, $y, 'water'), ($x, $y, 'water') [etc]

, 1 .

, , 400-500

+3

, 250 000 (250 000 ).

, , mysql 250 000 .

cf PHP + MySQL

mysql_query("START TRANSACTION");
... your requests
mysql_query("COMMIT");

, , , . , , ,.. .

+2

Obviously, since there are only 500 values ​​for the first statement, this is not as critical as you insert the values, you can do this using a loop.

INSERT INTO temp500 (value)
  VALUES (0), (1), (2), ... (499);
INSERT INTO map (x, y, terrain)
  SELECT x.value, y.value, 'water'
    FROM temp500 x, temp500 y;
0
source

More articles:


All Articles