1_readme.md

These are some benchmarks for various methods of using MySQL in PHP. They are used in this forum topic on webDevRefinery

BenchmarkRunner.php

<?php
/**
 * Simple PHP Benchmarking class
 * @author Daniel15 <dan.cx>
 */
class BenchmarkRunner
{
	private $className;
	
	public function __construct($className)
	{
		$this->className = $className;
	}
	
	/**
	 * Run the benchmark
	 * @param int Minimum number of iterations
	 * @param int Maximum number of iterations
	 * @param int Amount to increment by on each test.
	 */
	public function run($min, $max, $increment)
	{
		$class = new $this->className;
		// Let's use reflection to see the public methods inside it
		$classInfo = new ReflectionClass($this->className);
		$methods = $classInfo->getMethods(ReflectionMethod::IS_PUBLIC);
		
		// Output the header
		echo 'Benchmark,';
		for ($i = $min; $i < $max; $i += $increment)
		{
			echo $i, ',';
		}
		echo "\n";
		
		// Now let's do all the methods
		foreach ($methods as $method)
		{
			// Skip the constructor
			if ($method->name == '__construct')
				continue;
				
			$name = $method->name;
			echo $name, ',';
			for ($i = $min; $i < $max; $i += $increment)
			{
				$start = microtime(true);
					$class->$name($i);
				echo floor((microtime(true) - $start) * 1000), ',';
			}
			echo "\n";
		}
	}
}

mysql.php

<?php
require 'BenchmarkRunner.php';

class MysqlBenchmarks
{
	public function __construct()
	{
		$this->db = mysql_connect('localhost', 'root', 'password');
		mysql_select_db('benchmarks', $this->db);
	}
	
	public function mysql_query($iterations)
	{
		for ($i = 0; $i < $iterations; $i++)
		{
			mysql_query('SELECT * FROM products WHERE date > "2011-01-01"', $this->db);
		}
	}
	
	public function mysql_escape($iterations)
	{
		for ($i = 0; $i < $iterations; $i++)
		{
			mysql_query('SELECT * FROM products WHERE date > "' . mysql_real_escape_string('2011-01-01') . '"', $this->db);
		}	
	}
}

$runner = new BenchmarkRunner('MysqlBenchmarks');
$runner->run(0, 15000, 1000);
?>

mysqli.php

<?php
require 'BenchmarkRunner.php';

class MysqliBenchmarks
{
	public function __construct()
	{
		$this->db = new mysqli('localhost', 'root', 'password', 'benchmarks');
	}
	
	public function mysqli_query($iterations)
	{
		for ($i = 0; $i < $iterations; $i++)
		{
			$this->db->query('SELECT * FROM products WHERE date > "2011-01-01"');
		}
	}
	
	public function mysqli_escape($iterations)
	{
		for ($i = 0; $i < $iterations; $i++)
		{
			$this->db->query('SELECT * FROM products WHERE date > "' . $this->db->real_escape_string('2011-01-01') . '"');
		}	
	}
	
	public function mysqli_prepare_every_time($iterations)
	{
		$date = '2011-01-01';
		
		for ($i = 0; $i < $iterations; $i++)
		{
			$stmt = $this->db->prepare('SELECT * FROM products WHERE date > ?');
			$stmt->bind_param('s', $date);
			$stmt->execute();
			$stmt->close();
		}
	}
	
	public function mysqli_prepare_once($iterations)
	{
		$date = '2011-01-01';
		
		$stmt = $this->db->prepare('SELECT * FROM products WHERE date > ?');
		for ($i = 0; $i < $iterations; $i++)
		{
			$stmt->bind_param('s', $date);
			$stmt->execute();
		}
	}
	
	/*public function mysql_stored_proc($iterations)
	{
		for ($i = 0; $i < $iterations; $i++)
		{
			$result = $this->db->query('CALL ProductsSinceDate("2011-01-01")');
 			$result->close();
		}
	}*/
}

$runner = new BenchmarkRunner('MysqliBenchmarks');
$runner->run(0, 15000, 1000);
?>

pdo.php

<?php
require 'BenchmarkRunner.php';

class PDOBenchmarks
{
	public function __construct()
	{
		$this->db = new PDO('mysql:host=localhost;dbname=benchmarks', 'root', 'password');
	}
	
	public function pdo_query($iterations)
	{
		for ($i = 0; $i < $iterations; $i++)
		{
			$results = $this->db->query('SELECT * FROM products WHERE date > "2011-01-01"');
		}
	}
	
	public function pdo_prepare_every_time($iterations)
	{
		for ($i = 0; $i < $iterations; $i++)
		{
			$stmt = $this->db->prepare('SELECT * FROM products WHERE date > :date');
			$stmt->execute(array('date' => '2011-01-01'));
		}
	}
	
	public function pdo_prepare_once($iterations)
	{
		$stmt = $this->db->prepare('SELECT * FROM products WHERE date > :date');
		
		for ($i = 0; $i < $iterations; $i++)
		{
			$stmt->execute(array('date' => '2011-01-01'));
		}
	}
}

$runner = new BenchmarkRunner('PDOBenchmarks');
$runner->run(0, 15000, 1000);
?>

schema.sql

CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO products VALUES (1, 'Product 1', '2011-01-01'),(2, 'Product 2', '2011-12-10'),(3, 'Product 3', 2010-01-01);