chaining mysql queries

[hijinks]

So I have been coding php/mysql for around 7 years now and I have recently began to look at how things I do affect speeds of the webapp. I read somethinga bout chaining mysql queries together so I wrote a little benchmarking script and was shocked when i saw the results.. First here is the script

 

Code:

$iter = 100000;


$mysqli = new mysqli("localhost", "root", "", "test");
/* check connection */
if (mysqli_connect_errno()) {
  printf("Connect failed: %s\n", mysqli_connect_error());
  exit();
}




function getmtime() {
$a = explode (' ',microtime());
return(double) $a[0] + $a[1];
}

$start = getmtime();
for ($i = 0; $i < $iter; $i++) {
$mysqli->query("INSERT INTO employee_data(f_name,l_name,title,age,yos,salary) VALUES('Mike','Doe','CEO','100000','26','5')");
$mysqli->query("INSERT INTO employee_data(f_name,l_name,title,age,yos,salary) VALUES('Mike','Doe','CEO','100000','26','5')");
$mysqli->query("INSERT INTO employee_data(f_name,l_name,title,age,yos,salary) VALUES('Mike','Doe','CEO','100000','26','5')");
$mysqli->query("INSERT INTO employee_data(f_name,l_name,title,age,yos,salary) VALUES('Mike','Doe','CEO','100000','26','5')");
$mysqli->query("INSERT INTO employee_data(f_name,l_name,title,age,yos,salary) VALUES('Mike','Doe','CEO','100000','26','5')");
$mysqli->query("INSERT INTO employee_data(f_name,l_name,title,age,yos,salary) VALUES('Mike','Doe','CEO','100000','26','5')");

/*
$mysqli->query("
    INSERT INTO employee_data(f_name,l_name,title,age,yos,salary) VALUES('Mike','Doe','CEO','100000','26','5');
    INSERT INTO employee_data(f_name,l_name,title,age,yos,salary) VALUES('Mike','Doe','CEO','100000','26','5')
    INSERT INTO employee_data(f_name,l_name,title,age,yos,salary) VALUES('Mike','Doe','CEO','100000','26','5')
    INSERT INTO employee_data(f_name,l_name,title,age,yos,salary) VALUES('Mike','Doe','CEO','100000','26','5')
    INSERT INTO employee_data(f_name,l_name,title,age,yos,salary) VALUES('Mike','Doe','CEO','100000','26','5')
    INSERT INTO employee_data(f_name,l_name,title,age,yos,salary) VALUES('Mike','Doe','CEO','100000','26','5')
");
*/
}
$end = getmtime();

$time = $end - $start;

echo "the time: $time";

 

So the normal slow query is uncommented and when i run that it creates 600k new rows in around 55 seconds.. now if comment out the first part and uncomment the chaining section where my 6 queries get run in 1 mysqli->query call it gets run in 5 seconds.

 

Now to now chain your queries takes 10x longer.. so if you are running multiple updates or inserts per page.. make sure you chain them together!