[RESOLVED] A mysqli vs. mysql benchmark test

[void_function]

I've performed a small benchmark testing mysqli query vs. mysqli prepared statement performance. The following is the test environment:

Server:

Windows Vista Home Premium (32-bit)
Apache 2.2
PHP 5.2 (as Apache Module)
MySQL 5.0.41-community-nt
2GB RAM
Turion 64 X2 (dual core, 64-bit)
5400RPM SATA disc
Laptop

No accelerators used.

I tested two most common query formats, a simple one-field fetch with index, and a many-fields fetch with index. Index used was not primary. The tables:

test
-------------
	id - unsigned int, primary, autoincrement
	val - tinytext
	idx - unsigned int, index

test2
-------------
	id - unsigned int, primary, autoincrement
	int1 - unsigned int
	int2 - unsigned int
	int3 - unsigned int
	str1 - tinytext
	str2 - tinytext
	idx - unsigned int, index

Both tables using latin1_general_cs collation.

The following is the result of the test:

10000 x $db->query('INSERT INTO test (val, idx) VALUES ('$val', $idx)')............................................................... 7.4988
10000 x Full prepared statement per loop (prepare, bind, execute, close).............................................................. 9.9361
10000 x Prepared statement (only execute() in loop)................................................................................... 6.8879


10000 x $db->query('SELECT val FROM test WHERE idx=$idx LIMIT 1')..................................................................... 1.5757
10000 x Full prepared statement per loop (prepare, bind, execute, close).............................................................. 7.4765
10000 x Prepared statement (only execute(), fetch() and free_result() in loop)........................................................ 3.9755


10000 x $db->query('INSERT INTO test2 (`int1`, `int2`, `int3`, `str1`, `str2`, `idx`) VALUES (...)').................................. 11.4084
10000 x Prepared statement (only execute() in loop)................................................................................... 10.7078


10000 x $db->query('SELECT `int1`, `int2`, `int3`, `str1`, `str2`, `idx` FROM test2 WHERE `idx`=$idx LIMIT 1') -- fetch_assoc()....... 1.4132
10000 x Prepared statement (only execute(), fetch() and free_result() in loop)........................................................ 4.2010

The values on the right are times in seconds. The absolute times are irrelevant. The relative times are what is important for this test. While the test is not purely scientifical, ie. not all variables were controled, and not all situations were covered (acceleration, dedicated MySQL server, separation of HTTP server and MySQL server, etc...), and no test was performed for fixed-size vs. variable-size tables, the values above are indicative of the following:

Prepared statements have only slight speed boost during inserts, and only if same SQL format is used with parameters changed per iteration. Therefore, prepared statements in my opinion have no benefit over standard query EXCEPT in situations where multiple rows of basically same format are being inserted, AND when external data is inserted to avoid SQL injections.

With fetching, I find that fetch_row or fetch_assoc works faster than binding the result. This has to do with PHP overhead and function calling. With many parameters being fetch from a row, using an array with "standard" query is faster and easier to handle (ie. you can add or remove values only from the query, and that will be automatically reflected in the array structure).

MySQLi has many benefits over mysql. Primarily, I use it because of prepared statements to avoid SQL injections, and because I have extended the base class with exception handling so that in entire application there is single nexus of all SQL errors being handled and piped to appropriate sections of the code.

Variable size tables were used as these are most common in real applications. The PHP overhead with parsing strings for normal queries must also
be taken into consideration, so the tests is not pure mysqli vs. mysql analysis, but the analysis of performance in a PHP application.

Bottom line, other than avoiding SQL injections and having a slight boost with repeated inserts for same SQL format, prepared statements have no other benefit over standard (mysqli) query.

[void_function]

Below is the code that executed the test. The additional functions per iteration are kept constant, but the slightly variable size of data produced per iteration should resemble real-life applications. Also, random access parameters (index data and values) are to ensure minimum or no query caching so these should be direct results.

<?php

class myStmt extends mysqli_stmt {
	public function __construct ($mysqli, $query) {
		parent::__construct($mysqli, $query);	
	}

public function execute() {
	parent::execute();
	if ($ern= mysqli_stmt_errno($this)) {
		throw new Exception (mysqli_stmt_error($this), $ern);
	}			
}
}

class myMySQLi extends mysqli {
	public $dbQueries= 0;	// Number of performed queries

function __construct($serv, $user, $pass, $db) {
	parent::__construct ($serv, $user, $pass, $db);

	if ($ern= mysqli_connect_errno()) {
		throw new Exception (mysqli_connect_error(), $ern);
	}
}
function query ($query) {
	$res= parent::query ($query);
	if ($ern= mysqli_errno($this)) {
		$erm= mysqli_error($this);
		$trace= debug_backtrace();
		throw new Exception ("query('$query') called in {$trace[0]['file']}, line {$trace[0]['line']} :: $erm", $ern);
	} else $this->dbQueries++;
	return $res;		
}
function simple_query($query) {
	$res= $this->query($query);
	$res->close();			
}
function prepare ($query) {
	$res= new myStmt ($this, $query);
	if ($ern= mysqli_errno($this)) {
		$erm= mysqli_error($this);
		$trace= debug_backtrace();
		throw new Exception ("prepare('$query') called in {$trace[0]['file']}, line {$trace[0]['line']} :: $erm", $ern);
	} else $this->dbQueries++;
	return $res;		
}
}

function myRandomString($len) {

$str= '';
for ($i=0; $i<$len; $i++) {
	$seed= mt_rand (0, 2);
	switch ($seed) {
		case 0: $str.= mt_rand (0, 9); break;
		case 1: $str.= chr(mt_rand(0, 25)+65); break;
		case 2:
		default: $str.= chr(mt_rand(0, 25)+97); break;	
	}
}

return $str;	
}

header ('Content-type: text/plain');

$n= 10000;

$db= new myMySQLi('localhost', 'username', 'password', 'database');

$db->query('TRUNCATE `test`');
$db->query('TRUNCATE `test2`');

set_time_limit(360);

echo "$n x \$db->query('INSERT INTO test (val, idx) VALUES ('\$val', \$idx)')"; flush();
$stime= microtime(true);
for ($i=0; $i<$n; $i++) {
	$val= myRandomString(mt_rand(10, 200));
	$idx= mt_rand(1, 600);
	$res= $db->query("INSERT INTO test (val, idx) VALUES ('$val', $idx)");
}
echo "............................................................... ",sprintf('%.4f', microtime(true)-$stime),"\n";

echo "$n x Full prepared statement per loop (prepare, bind, execute, close)"; flush();
$stime= microtime(true);
for ($i=0; $i<$n; $i++) {
	$idx= mt_rand(1, 600);
	$val= myRandomString(mt_rand(10, 200));
	$stmt= $db->prepare('INSERT INTO test (val, idx) VALUES (?, ?)');
	$stmt->bind_param('si', $val, $idx);
	$stmt->execute();
	$stmt->close();
}
echo ".............................................................. ",sprintf('%.4f', microtime(true)-$stime),"\n";

echo "$n x Prepared statement (only execute() in loop)"; flush();
$stime= microtime(true);
$stmt= $db->prepare('INSERT INTO test (val, idx) VALUES (?, ?)');
$stmt->bind_param('si', $val, $idx);	
for ($i=0; $i<$n; $i++) {
	$idx= mt_rand(1, 600);
	$val= myRandomString(mt_rand(10, 200));
	$stmt->execute();
}
$stmt->close();
echo "................................................................................... ",sprintf('%.4f', microtime(true)-$stime),"\n";



echo "\n\n";



echo "$n x \$db->query('SELECT val FROM test WHERE idx=\$idx LIMIT 1')"; flush();
$stime= microtime(true);
for ($i=0; $i<$n; $i++) {
	$idx= mt_rand(1, 600);
	$res= $db->query("SELECT val FROM test WHERE idx=$idx LIMIT 1");
	$row= $res->fetch_row();
	$res->free_result();
}
echo "..................................................................... ",sprintf('%.4f', microtime(true)-$stime),"\n";

echo "$n x Full prepared statement per loop (prepare, bind, execute, close)"; flush();
$stime= microtime(true);
for ($i=0; $i<$n; $i++) {
	$idx= mt_rand(1, 600);
	$stmt= $db->prepare("SELECT val FROM test WHERE idx=? LIMIT 1");
	$stmt->bind_param('i', $idx);
	$stmt->execute();
	$stmt->bind_result($row);
	$stmt->fetch();
	$stmt->free_result();
	$stmt->close();
}
echo ".............................................................. ",sprintf('%.4f', microtime(true)-$stime),"\n";

echo "$n x Prepared statement (only execute(), fetch() and free_result() in loop)"; flush();
$stime= microtime(true);
$stmt= $db->prepare("SELECT val FROM test WHERE idx=? LIMIT 1");
$stmt->bind_param('i', $idx);
$stmt->bind_result($row);		
for ($i=0; $i<$n; $i++) {
	$idx= mt_rand(1, 600);
	$stmt->execute();
	$stmt->fetch();
	$stmt->free_result();
}
$stmt->close();
echo "........................................................ ",sprintf('%.4f', microtime(true)-$stime),"\n";



echo "\n\n";



echo "$n x \$db->query('INSERT INTO test2 (`int1`, `int2`, `int3`, `str1`, `str2`, `idx`) VALUES (...)')"; flush();
$stime= microtime(true);
for ($i=0; $i<$n; $i++) {
	$int1= mt_rand(1, 600000);
	$int2= mt_rand(1, 600000);
	$int3= mt_rand(1, 600000);
	$str1= myRandomString(mt_rand(10, 200));
	$str2= myRandomString(mt_rand(10, 200));
	$idx= mt_rand(1, 100);
	$res= $db->query("INSERT INTO test2 (`int1`, `int2`, `int3`, `str1`, `str2`, `idx`) VALUES ($int1, $int2, $int3, '$str1', '$str2', $idx)");
}
echo ".................................. ",sprintf('%.4f', microtime(true)-$stime),"\n";

echo "$n x Prepared statement (only execute() in loop)"; flush();
$stime= microtime(true);
$stmt= $db->prepare('INSERT INTO test2 (`int1`, `int2`, `int3`, `str1`, `str2`, `idx`) VALUES (?, ?, ?, ?, ?, ?)');
$stmt->bind_param('iiissi', $int1, $int2, $int3, $str1, $str2, $idx);	
for ($i=0; $i<$n; $i++) {
	$int1= mt_rand(1, 600000);
	$int2= mt_rand(1, 600000);
	$int3= mt_rand(1, 600000);
	$str1= myRandomString(mt_rand(10, 200));
	$str2= myRandomString(mt_rand(10, 200));
	$idx= mt_rand(1, 100);
	$stmt->execute();
}
$stmt->close();
echo "................................................................................... ",sprintf('%.4f', microtime(true)-$stime),"\n";



echo "\n\n";



echo "$n x \$db->query('SELECT `int1`, `int2`, `int3`, `str1`, `str2`, `idx` FROM test2 WHERE `idx`=\$idx LIMIT 1') -- fetch_assoc()"; flush();
$stime= microtime(true);
for ($i=0; $i<$n; $i++) {
	$idx= mt_rand(1, 100);
	$res= $db->query("SELECT `int1`, `int2`, `int3`, `str1`, `str2`, `idx` FROM test2 WHERE `idx`=$idx LIMIT 1");
	$row= $res->fetch_assoc();
	$res->free_result();
}
echo "....... ",sprintf('%.4f', microtime(true)-$stime),"\n";

echo "$n x Prepared statement (only execute(), fetch() and free_result() in loop)"; flush();
$stime= microtime(true);
$stmt= $db->prepare("SELECT `int1`, `int2`, `int3`, `str1`, `str2`, `idx` FROM test2 WHERE `idx`=? LIMIT 1");
$stmt->bind_param('i', $idx);
$stmt->bind_result($int1, $int2, $int3, $str1, $str2, $idx2);
$idx2=0;
for ($i=0; $i<$n; $i++) {
	$idx= mt_rand(1, 100);
	$stmt->execute();
	$stmt->fetch();
	$stmt->free_result();
}
$stmt->close();
echo "........................................................ ",sprintf('%.4f', microtime(true)-$stime),"\n";


?>

[void_function]

I apologize, the title of the thread is misleading, I was talking to a friend about doing additional comparison with mysql, while posting this, and I confused myself.

The test is NOT mysql vs. mysqli but mysqli query vs. prepared statement.

[Sxooter]

While this is interesting, it's rather limited.

I assume, since you didn't say, that you're testing myisam tables only. It would be interesting to see the difference in results, if any, from using innodb handler.

Also, prepared statements sometimes help for really complex select queries, where the preparation of the query can take hundreds of milliseconds or more to complete, so you might want to test some other types of queries to see if prepared queries does help there.

My experience has been that prepared queries can really help for only a few cases from a performance perspective.

But they make avoiding SQL injection so easy that even if they were never faster, I'd still use them for any application that had a public interface.

[MarkR]

I've no idea if the mysqli functions have the capability to emulate prepared statements rather than executing them natively; if they do, this may improve performance (counter-intuitively), particularly for queries which are prepared then executed only once.

I've seen some evidence that under PDO, turning off emulation of prepared statements actually decreases performance.

One reason for this may be that mysql's query cache (which is very stupid, but very, very fast) doesn't have any effect on prepared queries.

In a web application, you often run the exact same query again when no data has changed - typically because this is running from another thread / process / server which has no way to cache the information, and/or you want the data to be completely current (e.g. querying user data in each page request). Such queries are optimised very well by the query cache.

Mark