mysqli::multi_query() performance

[Brian W]

A few questions that if I was more knowledgeable about MySQL (such as the query log that I've heard of).

- Does mysqli::multi_query() make multiple requests to the server or just one?

- If it is just splitting the queries on the semicolon and then making multiple requests, is it still faster than looping and querying in PHP?

- Does it take less memory than, lets say, mysql_query() foreach query?

- Is it faster than, lets say again, mysql_query() looped?

 

The last 2 I can probably just benchmark myself but I'm hoping someone will know off had.

 

Background (blah blah blah)

I'm working on a lean rapid development framework for myself (and maybe others eventually) to use. Much like Cake and other frameworks it does a lot of queries, often times more then you need. The sacrifice of course is performance VS ease of development, but I'm trying to not make a martyr out of my framework :-)

 

I've observed on many occasions that simple queries can take more time than a complex query  on seemingly random occasions. My understanding is that this is because of the connection, not the real amount of time it took for the server to query. I figure if I avoid multiple requests to the server I will cut down on the time everything takes. My theory is based on the same concept of combining all your CSS and JS files into single files to avoid dozens of HTTP requests which slow down page load time.

[Brian W]

I decided to do the benchmarking since no one has replied at this point. I'm not 100% confident that I've got the right idea for benchmarking queries, but I gave it a shot.

The result: mysqli::multi_query() is faster than mysql_query();

 

<pre>
<?php
$host = "********";
$user = "********";
$pass = "********";
$db   = "********";

$i=0; $result=array();
$mysqli = new mysqli($host, $user, $pass, $db);
while($i<200){
    $sql1 = "SELECT * FROM user WHERE id = ".rand(1,9999).";";
    $sql2 = "SELECT * FROM user WHERE id = ".rand(1,9999).";";
    $start = microtime(true);
    $mysqli->multi_query($sql1.$sql2);
    $result[$i++] = (microtime(true)-$start);
}
echo (array_sum($result));
echo "\n";
echo (array_sum($result)/count($result));
echo "\n";

$i=0; $result=array();
mysql_connect($host, $user, $pass); mysql_select_db($db);
while($i<200){
    $sql1 = "SELECT * FROM user WHERE id = ".rand(1,9999).";";
    $sql2 = "SELECT * FROM user WHERE id = ".rand(1,9999).";";
    $start = microtime(true);
    mysql_query($sql1); mysql_query($sql2);
    $result[$i++] = (microtime(true)-$start);
}
echo (array_sum($result));
echo "\n";
echo array_sum($result)/count($result);
?>
</pre>

 

My results:

0.0062904357910156 //Total for mysqli::multi_query()
3.1452178955078E-5 //Average for mysqli::multi_query()  - - umm, that aint human speak :-p

4.9394555091858 //Total for mysql_query()
0.024697277545929 //Average for mysql_query()

 

According to those results, mysqli::multi_query() is much more efficient. Again, not 100% confident in my benchmarking methods.

[Brian W]

Results from doing "while($i<1000)"

 

0.0076823234558105
7.6823234558105E-6

34.950205564499
0.034950205564499