Test the speed of PDO versus MySQLi

pdo-vs-mysqli.php

<?php
/* Script to test the speed of PDO versus MySQLi
 * Written by Jim Westergren, info https://www.jimwestergren.com/pdo-versus-mysqli
 * I release this code to public domain.
 * Please give attribution by name and link but not required
 */
define('MYSQL_HOST', ''); 
define('MYSQL_USERNAME', ''); 
define('MYSQL_PASSWORD', ''); 
define('MYSQL_DATABASE', '');

// Start with a low number
define('NUMBER_OF_QUERIES_TO_TEST', 100); // 5000

// MySQLi connection
$conn_mysqli = new mysqli(MYSQL_HOST, MYSQL_USERNAME, MYSQL_PASSWORD, MYSQL_DATABASE, 3306);
$conn_mysqli->set_charset('utf8');

// PDO connection
$dsn = "mysql:host=".MYSQL_HOST.";dbname=".MYSQL_DATABASE.";charset=utf8mb4";
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => FALSE, // 80% speed increase if set to TRUE. Changed to TRUE after test 2
];
$conn_pdo = new PDO($dsn, MYSQL_USERNAME, MYSQL_PASSWORD, $opt);


// Test 1 using PDO with query + quote
$start_time = microtime(true);
for ($i = 0; $i < NUMBER_OF_QUERIES_TO_TEST; $i++) {	
	// Random string to bypass the query cache
	$domain = bin2hex(openssl_random_pseudo_bytes(10));
	$conn_pdo->quote($domain);
	$stmt = $conn_pdo->query("SELECT domain FROM domains WHERE domain = '{$domain}' LIMIT 1");
	$row = $stmt->fetch();
}
$time_it_took = round(microtime(true) - $start_time, 3);
echo "test 1 (PDO with query + quote) finished in ".$time_it_took." seconds<br>\n";
echo str_pad('',4096)."\n"; ob_flush(); flush(); sleep(2);

// Test 2 using PDO with prepared statement
$start_time = microtime(true);
for ($i = 0; $i < NUMBER_OF_QUERIES_TO_TEST; $i++) {	
	$domain = bin2hex(openssl_random_pseudo_bytes(10));
	$stmt = $conn_pdo->prepare("SELECT domain FROM domains WHERE domain = ? LIMIT 1");
	$stmt->execute([$domain]);
	$row = $stmt->fetch();
}
$time_it_took = round(microtime(true) - $start_time, 3);
echo "test 2 (PDO with prepared statement) finished in ".$time_it_took." seconds<br>\n";
echo str_pad('',4096)."\n"; ob_flush(); flush(); sleep(2);

// Enable the emulation of prepares
$conn_pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, TRUE);

// Test 3 using PDO with emulation with query + quote
$start_time = microtime(true);
for ($i = 0; $i < NUMBER_OF_QUERIES_TO_TEST; $i++) {	
	// Random string to bypass the query cache
	$domain = bin2hex(openssl_random_pseudo_bytes(10));
	$conn_pdo->quote($domain);
	$stmt = $conn_pdo->query("SELECT domain FROM domains WHERE domain = '{$domain}' LIMIT 1");
	$row = $stmt->fetch();
}
$time_it_took = round(microtime(true) - $start_time, 3);
echo "test 3 (PDO with emulation with query + quote) finished in ".$time_it_took." seconds<br>\n";
echo str_pad('',4096)."\n"; ob_flush(); flush(); sleep(2);

// Test 4 using PDO with prepared statement
$start_time = microtime(true);
for ($i = 0; $i < NUMBER_OF_QUERIES_TO_TEST; $i++) {	
	$domain = bin2hex(openssl_random_pseudo_bytes(10));
	$stmt = $conn_pdo->prepare("SELECT domain FROM domains WHERE domain = ? LIMIT 1");
	$stmt->execute([$domain]);
	$row = $stmt->fetch();
}
$time_it_took = round(microtime(true) - $start_time, 3);
echo "test 4 (PDO with emulation with prepared statement) finished in ".$time_it_took." seconds<br>\n";
echo str_pad('',4096)."\n"; ob_flush(); flush(); sleep(2);


// Test 5 using MySQLi with real_escape_string
$start_time = microtime(true);
for ($i = 0; $i < NUMBER_OF_QUERIES_TO_TEST; $i++) {
	$domain = bin2hex(openssl_random_pseudo_bytes(10));
	$domain = $conn_mysqli->real_escape_string($domain);
	$row = mysqli_fetch_assoc(mysqli_query($conn_mysqli, "SELECT domain FROM domains WHERE domain = '".$domain."' LIMIT 1;"));
}	
$time_it_took = round(microtime(true) - $start_time, 3);
echo "test 5 (MySQLi with real_escape_string) finished in ".$time_it_took." seconds<br>\n";
echo str_pad('',4096)."\n"; ob_flush(); flush(); sleep(2);

// Test 6 using MySQLi with prepared statement
$start_time = microtime(true);
for ($i = 0; $i < NUMBER_OF_QUERIES_TO_TEST; $i++) {
	$domain = bin2hex(openssl_random_pseudo_bytes(10));
	$stmt = $conn_mysqli->prepare("SELECT domain FROM domains WHERE domain = ? LIMIT 1;");
	$stmt->bind_param('s', $domain);
	$stmt->execute();
	$row = $stmt->get_result()->fetch_array(MYSQLI_ASSOC);
	$stmt->free_result();
}	
$time_it_took = round(microtime(true) - $start_time, 3);
echo "test 6 (MySQLi with prepared statement) finished in ".$time_it_took." seconds<br>\n";
echo str_pad('',4096)."\n"; ob_flush(); flush(); sleep(2);