Page 1 of 1

Inserts in MySQL

Posted: Sat Jun 11, 2005 6:49 am
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.

Posted: Mon Jun 13, 2005 12:36 pm
by Sphen001
Thanks for that script. I never knew about that feature. :D

Sphen001

Posted: Mon Jun 13, 2005 4:48 pm
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.