Do you happen to have functions that read all rows of a database result into one array, without processing the rows, and pass the array to other functions? For example, do you fetch results in your database class and pass them to a template engine displaying? If so, here is excellent news for you. mysqli_fetch_all(), which comes with mysqlnd, does the task of fetching the data sometimes twice as fast as mysqli_fetch_array(). Reason being: it saves a loop with function calls…

mixed mysqli_fetch_all( mysqli_result $result [, int $resulttype] )

The new API call, which is only available if you build ext/mysqli with mysqlnd support, does fetch all rows of a result set into an array with just one function call:

$all_rows = mysqli_fetch_all($res);

The one mysqli_fetch_all() call does exactly the same as the following loop:

$all_rows = array(); 
while ($all_rows[] = mysqli_fetch_array($res)) 
  ;

In my testing I found result sets for which mysqli_fetch_all() took only 60% of the execution time of the mysqli_fetch_array() variant. At the first glance this sounds great, but you need to be aware of what it means to get all results in one array.

First of all, some trivia: mysqli_fetch_all() creates a large data structure which may consume a lot of memory. With the loop variant you are processing the result set on a row by row basis. This means you are kind of streaming the data and you work on small chunks which require less memory. With mysqli_fetch_all() you fetch everything at once – as one big chunk.

Second and most important, although another trivia, you lose a loop. With mysqli_fetch_array() you get a loop “for free” which you can use to post process your database results. This loop is gone when using mysqli_fetch_all(). If you, for whatever reason, need to iterate over the result set, go for mysqli_fetch_array(). Using mysqli_fetch_all() and an additional foreach() loop for post processing will be notably slower than using mysqli_fetch_array().

mysqli_fetch_all() - Fetch all results at once

Procedural style:
mixed mysqli_fetch_all( mysqli_result $result [, int $resulttype] )

Object oriented style (method):
class mysqli_result {
  mixed fetch_all ( [int $resulttype] )
}

$result -   
  Procedural style only: A result set identifier returned by 
  mysqli_query(), mysqli_store_result() or mysqli_use_result().

$result_type - 
  This optional parameter is a constant indicating what type 
  of array should be produced from result set. 
  The possible values for this parameter are the constants 
  MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH. 
  Defaults to MYSQLI_BOTH.

Having said that, you will not be surprised to hear that it depends on your application if mysqli_fetch_all() is of any use for you. If you are always fetching all results into an array and pass this array from one layer – say the DB layer – to another layer – say the the template engine – without post processing the database rows before passing, then mysqli_fetch_all() is for you. If you are not passing arrays from one layer to another but iterators (to save memory) or if you are not using separate layers for fetching and processing, then it is unlikely that you benefit from the new API call.

Be warned: different setups, different results…

Ok, so how much will you profit from mysqli_fetch_all()? It depends, I don’t know, I don’t want to promise too much…

In the past, I’ve had many argues with team members about benchmark results. My results did not confirm those of the other team members. I found that one reason for this can be different hardware setups. Even if everybody of the team uses the same operating system, the same MySQL Server version, the same PHP and mysqlnd version, the same compiler version, the same whatever, we are sometimes getting different results. This makes me become a bit careful whenever I post results.

To demonstrate the impact of different hardware, I’ve run a mysqli_fetch_assoc() benchmark script on all computers I own. I found that the fastest and the slowest hardware setup differ by 7%. Not that much? Wrong. It’s huge if the performance difference you expect is only some 10%… So, one last time: run your own tests, run your own benchmarks.

I have four different computers at home. Each of them has about 1GB of RAM and runs OpenSUSE 10.2, PHP 5.2.4-dev, MySQL 5.1.20.

1. Laptop – Core2Duo, T7200 – VMWare with OpenSUSE 10.2
2. Laptop – Pentium M 1.5Ghz – Dual-Boot with OpenSUSE 10.2
3. Desktop – Pentium 4 2.8Ghz – OpenSUSE 10.2
4. Desktop – AMD Athlon 64 3000+ 1.8 Ghz – OpenSUSE 10.2

I wrote a little benchmark script for mysqli_fetch_all() and did run it on all my computers. I made sure that no other programs have been running while executing the benchmark and increased the priority of the benchmark process (VMWare process set to Realtime priority plus nice -10), if several subsequent runs gave me different results. In the end, all computers gave me constant results. I did far more runs that I show here, thus I’m confident that the setup was OK.

Computer	Run 1	Run 2	Run 3	Average
Core2Duo, T7200	62%	60%	57%	60%
Pentium M 1.5Ghz	54%	54%	54%	54%
Pentium 4 2.8Ghz	58%	61%	59%	59%
AMD Athlon 64 3000+	60%	61%	61%	61%

Figures less than 100% mean that mysqli_fetch_all() operated faster than mysqli_fetch_assoc() called in a loop.Here is the script I used.

<php
error_reporting(E_ALL);
mysqli_report(MYSQLI_REPORT_OFF);
mysqli_report(MYSQLI_REPORT_STRICT);

printf("\nmysqli_fetch_all() performance compared to mysqli_fetch_assoc().\n\n");
printf("mysqli_fetch_assoc execution time is set to 100%%,\n");
printf("mysqli_fetch_all() execution time is shown in relation to that.\n");
printf("&glt: 100%% means that mysqli_fetch_assoc() was faster than mysqli_fetch_assoc().\n\n");
printf("%s\n", str_repeat('-', 60));

try {
	$aggregated_time_fetch_all = 0;
	$aggregated_time_fetch_assoc = 0;

	$link = mysqli_connect('localhost', 'root', 'root', 'test');
	for ($char_len = 10; $char_len < 65000; $char_len *= 2) {

		mysqli_query($link, 'DROP TABLE IF EXISTS test');
		mysqli_query($link, sprintf('CREATE TABLE test(id INT, label VARCHAR(%d))', $char_len));

		$query = sprintf("INSERT INTO test(label) VALUES ('%s')",
			str_repeat('a', $char_len));
		for ($i = 0; $i < 100; $i++)
			mysqli_query($link, $query);
		// let the server cool down for a second
		sleep(1);

		$res0 = mysqli_query($link, 'SELECT id, label FROM test');
		$result0 = $res0->fetch_all(MYSQL_ASSOC);
		mysqli_free_result($res0);
		sleep(1);

		$time_fetch_all = 0;
		for ($i = 0; $i < 2; $i++) {
			$res1 = mysqli_query($link, 'SELECT id, label FROM test');
			$result1 = array();
			$start = microtime();
			$result1 = mysqli_fetch_all($res1, MYSQL_ASSOC);
			$time_fetch_all += microtime() - $start;
			mysqli_free_result($res1);
		}
		$aggregated_time_fetch_all += $time_fetch_all;
		sleep(1);

		$time_fetch_assoc = 0;
		for ($i = 0; $i < 2; $i++) {
			$res2 = mysqli_query($link, 'SELECT id, label FROM test');
			$result2 = array();
			$start = microtime();
			while ($result2[] = mysqli_fetch_assoc($res2))
				;
			$time_fetch_assoc += microtime() - $start;
			mysqli_free_result($res2);
		}
		$aggregated_time_fetch_assoc += $time_fetch_assoc;

		if ($time_fetch_all < $time_fetch_assoc)
			printf("- VARCHAR(%05d) - %03d%% -> mysqli_fetch_all() was faster\n",
				$char_len,
				(100 / $time_fetch_assoc) * $time_fetch_all);
		else 
			printf("+ VARCHAR(%05d) - %03d%% -> mysqli_fetch_all() was slower\n",
				$char_len,
				(100 / $time_fetch_assoc) * $time_fetch_all);

	}
	mysqli_close($link);

	printf("%s\n", str_repeat('-', 60));
	printf("Overall: %03d%%\n\n",
		(100 / $aggregated_time_fetch_assoc) * $aggregated_time_fetch_all);

} catch (mysqli_sql_exception $e) {
	printf("%s\n", $e->getMessage());
}
?>