Quickly insert 250k rows

Question

So I am trying to insert 250 000 rows in to my database. As this is just sample data I just need to query a x, y variable that does change thus the for loops and just the terrain which is simply 'water'

But this is taking forever. Anyone know how I can make this happen 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')");
    }
}

Answer 1

You might do better using the MySQL LOAD DATA FROM INFILE statement. Batch loading tends to be faster than constructing massive insert statement strings. Write your data to file and do a single LOAD.

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

Insert/Load also depends on the number of indexes on the table. Create indexes only on the necessary columns. You'll also often get better performance by adding the indexes after the data is loaded into the table so that you're not updating indexes while adding data.

Answer 2

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

So you will need only 1 query.

Of course split the query into multiple query when you have added 400-500 values