Inserts in MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Inserts in MySQL

Post by onion2k »

I recently had to look over some code written an ex-colleague that imports large quantities of data into a database. I was pretty horrified to see that it had been written using ordinary inserts rather than MySQL's extended insert functionality. After asking around a few of my fellow PHPers I learnt that lots of people don't make use of it.. So I whipped up a very simple script to demonstrate why it's a good idea. I thought I'd share it.

Extended insert syntax is quite simple. In a normal insert you'd use something like:

Code: Select all

insert into testtable (counter) values ('1');
In an extended insert you can add extra values in a list after the first one, eg:

Code: Select all

insert into testtable (counter) values ('1'),('2'),('3'),('4'),('5');
This enables you to do 5 inserts with a single database query. It's much, much faster.

The script:

Code: Select all

<?php

	############################################################################
	# Configuration
	############################################################################
	//Database details
		$db_host = "localhost";
		$db_user = "user";
		$db_password = "password";
		$db_name = "testdb";
	//Total number of inserts to the database for each type
		$inserts = 10000;
	//Break up extended inserts into chunks to avoid exceeding max_packet_size
		$extended_granularity = 100;
	//First part of the insert statement, standard between normal and extended
		$insert_sql = "insert into testtable (counter) values "; 
	############################################################################

	############################################################################
	# Connect to the database
	############################################################################
	$databaseLink = mysql_connect($db_host,$db_user,$db_password);
	mysql_select_db($db_name,$databaseLink);	
	############################################################################

	############################################################################
	# Normal inserts. Loops round $inserts times doing one insert per loop
	############################################################################
	$normal_start = microtime_float();
	for ($x=0;$x<$inserts;$x++) {
		$sql = $insert_sql."('".$x."')";
		mysql_query($sql,$databaseLink);
	}
	$normal_end = microtime_float();
	############################################################################

	############################################################################
	# Extended inserts. Does one insert per $inserts/$extended_granularity loop
	############################################################################
	$extended_start = microtime_float();
	for ($x=0;$x<$inserts;$x++) {
		$data[] = "('".$x."')"; // Builds an array of data
		if ($counter++ >= $extended_granularity) {
			$counter = 0;
			$sql = "insert into testtable (counter) values ";
			$sql .= implode(",",$data); //Concat data array into sql data
			mysql_query($sql,$databaseLink);
			$data = array();
		}
	}
	//Catch any inserts in $data if $inserts/$extended_granularity wasn't an int
	if ($counter > 0) {
			$counter = 0;
			$sql = $insert_sql.implode(",",$data);
			mysql_query($sql,$databaseLink);
			$data = array();
	}
	$extended_end = microtime_float();
	############################################################################

	############################################################################
	# Output results
	############################################################################
	echo "Normal:".($normal_end - $normal_start)."<br>";
	echo "Extended:".($extended_end - $extended_start)."<br>";
	############################################################################

	############################################################################
	# Misc functions
	############################################################################
	function microtime_float() {
	   list($usec, $sec) = explode(" ", microtime());
	   return ((float)$usec + (float)$sec);
	}
	############################################################################

?>
On my local server it works out approx 10 times faster for the full 10,000 inserts, for only a tiny increase in the complexity of the code.

Ace.
Sphen001
Forum Contributor
Posts: 107
Joined: Thu Mar 10, 2005 12:24 pm
Location: Land of the Beaver

Post by Sphen001 »

Thanks for that script. I never knew about that feature. :D

Sphen001
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Amen brother!

I use extended inserts as much as I can. Not only is it faster on the database side, but there's less overhead for making a database connection, sending queries, and handling results for each query.

Good thing to mention.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply