Best way of executing multiple MySQL queries in one go? 1

[LaundroMat]

Hi, I'm initializing a database with random numbers, and I noticed INSERTing the data into my mySQL database does take quite some time (especially considering the amount of data I want to enter).

Here's the script:

for ($x = 1; $x <= SIZE_X ; $x++) {
	for ($y = 1; $y <= SIZE_Y ; $y++) {
		$amt = mt_rand(MIN_AMOUNT, MAX_AMOUNT);
		$r = $amt*mt_rand(0,100);
		$g = $amt*(mt_rand(0,100-$r));
		$b = $amt*(100-$r-$g);
	
		$sql = "INSERT INTO tile (x, y, r, g, b) VALUES ($x, $y, $r, $g, $b)";
		$db->query($sql);
	}
}

Now, for a SIZE_X and SIZE_Y of 10 each, 100 INSERT queries have been sent. This takes some time... What would be the fastest way of doing this?

Thanks in advance!

 

[sleipnir214]

Insufficient data for a meaningful answer.

What database system are you using? This will certainly be a factor in any answer.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[LaundroMat]

I'm afraid I don't understand the question. I'm just running things on my home PC here, but eventually all should be run on a webserver somewhere that has PHP and mySQL. Seeing that the project is a)hobbyist and b)shouldn't cost me too much money, I'm afraid the database system will the most basic set-up imaginable.

 

[sleipnir214]

I'm sorry. You stated you were using the MySQL database system in your original post and I missed it.


How many rows are we talking about inserting?


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[LaundroMat]

Heh, no problem.

The amount of rows depends really... I'd like to be able to have about 1.000x1.000 fields, or even more. It all depends on how the project turns out (ie many participants, or not).

 

[sleipnir214]

First, you cannot run more than one query at a time in a single invokation of mysql_query() or mysqli_query() in PHP. PHP simply won't allow it.

That said, it is possible to insert multiple rows using a single query. A query of the form:

INSERT INTO tablename (columname1, columnname2) VALUES (val1, val2), (val3, val4), .....

should do it. For details, see

http://dev.mysql.com/doc/mysql/en/insert.html

The quickest way to get lots of data into MySQL is to import the data from a text file using MySQL's LOAD DATA query. In your case, you might write your data to a file, then tell MySQL to import the data. See

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

for more details. Keep in mind that the INFILE keyword is used only when transmitting the datafile to MySQL through the database connection. You won't use the keywork from within PHP.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[LaundroMat]

Great! This is exactly what I was looking for. Thanks!