CODEWITHSUNDEEP
phpmysql
josmith
josmith

Reputation: 1049

Quickly insert 250k rows

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')");
    }
}

Upvotes: 5

Views: 505

Answers (4)

Girish Rao
Girish Rao

Reputation: 2669

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.

Upvotes: 8

Jerome WAGNER
Jerome WAGNER

Reputation: 22422

You should make sure that you are not running those 250.000 in auto-commit mode (250.000 transactions).

By sending them all in one transaction, you will speed things up quite a bit because the mysql engine will not have to invoke its transactional mechanisms 250.000 times.

cf PHP + MySQL transactions examples

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

If you don't care about the state of the indexes, you should also remove the indices you have on the table before starting the transaction and recreate them after. This will speed things up a lot also since the engine will not need to check unicity, update indices,.. for each row.

Upvotes: 2

Neil
Neil

Reputation: 55392

Obviously since there are only 500 values for the first statement it's not quite so critical how you insert the values, you could 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;

Upvotes: 0

dynamic
dynamic

Reputation: 48101

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

Upvotes: 3

Related Questions