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!

[anyweb]

how can I apply something like this to my apache/mysql setup ?

[znx]

Impressive tip, I normally just place calls as and how I need them, I can see now thats a flaw, I will build chains!

 

how can I apply something like this to my apache/mysql setup ?

 

This is more about coding style rather than the link between apcahe and mysql. Effective this shows that placing multiple queries to the MySQL server in one batch will improve your performance massively.

[noriko]

how can I apply something like this to my apache/mysql setup ?

 

 

kool tip dude ... i don't currently use databases for anything, as i find them somewhat unnecessary... but i do develop a type of hybrid flatfile website engine ..(not really a cms unless you add modules to it) - - - and but i aim to make it as fast as possible so teh mod maker can be as careless as s/he wants without too much performance cuts... anyways.. i always thought it was obvious that making a call to a database will cost extra time... therefore doing it multiple times will clearly multiply that initial time ... but good on you for pointing it out to these who did hadn't thought that deeply into things ..lol .. i do that with just about everything.. sometimes i think I'm going crazy and have been told that on many many occasions even be y school tutors who are suppose to be sensitive.. mutter fukkers !