My Blog

I was programming some code that needed to do a lot of inserts a few months back. I hypothesized that creating a big SQL insert statement would be faster than executing a bunch of small insert statements. I didn’t have time to create a test, but I wanted to go back and test this theory at some point. The idea was that even though there is a penalty for looping through an array and accessing a variable to build the SQL statement that it would still be faster than asking PHP to send a bunch of tiny querries.

My test server is my local machine running Ubuntu 10.04, dual 1.80Ghz core Intel processors with 2 GB of memory. Its running the latest stable release of PHP5, Apache2, and MySql5. I used the standard php mysql_connect function, not mysqli. The database engine used in this test was MyIsam. I performed 1000 inserts in the first test, I then altered the code to build one giant insert. I restarted the apache and mysql server after the first scenario.

Test 1

1
2
3
4
5
6
7
8
9
10
11

<?php
$s = microtime(true);
mysql_connect('localhost','root','password') or die(mysql_error());
mysql_select_db('test');
for($i=0;$i<1000;$i++){
	mysql_query("INSERT INTO test SET test_text='hello world'");
}
$e = microtime(true);
 
echo $e-$s;
?>

Results (in seconds)

• 0.075281143188477
• 0.064616918563843
• 0.08400297164917
• Average: 0.074633678

Test 2

1
2
3
4
5
6
7
8
9
10
11
12
13

<?php
$s = microtime(true);
mysql_connect('localhost','root','password') or die(mysql_error());
mysql_select_db('test');
$sql = "INSERT INTO test (`test_id`,`test_text`) VALUES ";
for($i=0;$i<1000;$i++){
	$sql.= "(NULL,'hello world'),";
}
mysql_query(substr($sql,0,-1));
$e = microtime(true);
 
echo $e-$s;
?>

Results

• 0.0051629543304443
• 0.00099396705627441
• 0.00093698501586914
• Average: 0.002364635

So my hypothesis was correct. Its definitely faster to send one big query than a bunch of small queries. This was pretty unscientific though and its unknown what would’ve happened had I done say 10,000 inserts or had my insert statements been bigger.

The same logic can be applied to SELECTS using the WHERE IN clause. When looping through an array rather than issuing a query at each iteration, change your code to use a WHERE IN and send one large SELECT statement.

Did you find this blog helpful? Please reply with any questions or comments. Thanks for reading.

Related posts:

1. SQL_CALC_FOUND_ROWS – Get Total Rows in MySQL Query
2. Manage Multiple Shell Sessions with Screen

Tags: mysql, mysql insert, mysql optimization, query optimization

This entry was posted on Monday, May 31st, 2010 at 11:46 am and is filed under Programming, SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.