Inserts in MySQL
Posted: Sat Jun 11, 2005 6:49 am
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:
In an extended insert you can add extra values in a list after the first one, eg:
This enables you to do 5 inserts with a single database query. It's much, much faster.
The script:
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.
Extended insert syntax is quite simple. In a normal insert you'd use something like:
Code: Select all
insert into testtable (counter) values ('1');Code: Select all
insert into testtable (counter) values ('1'),('2'),('3'),('4'),('5');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);
}
############################################################################
?>Ace.