Mysqli vs Mysql vs PDO Performance Benchmark Results – PHP & MySQL

The technology world is all about options, so when looking into the different ways you have to connect to a MySQL database when working with PHP, it’s no different.

There are three major ways to connect to MySQL databases with PHP - the good old mysql API, and the newer mysqli and pdo.

There are a lot to compare between these three, in many aspects such as security, testability, and other. But, in this article, we’ll deep dive into one of the aspects - performance.

So what’s a better way than to just create an experiment to benchmark mysqli vs pdo vs mysql and see the performance results when running queries through each of them?

Experiment Setup & Process

To set up our experiment, we took IMDB’s titles database and extract around 50MB of data from that into a table called movies, with a column named title. There are 320,000 records in that table now.

We wanted to have a very basic and clean experiment, without performing any structure changes such as indexing the column. Some of you might argue that it can hurt the experiment’s ability to reflect real performance results, but we believe that as long as we compare apples to apples, we have some good indication to start with.

We wrote some code that connects the MySQL database with all three approaches, runs in a loop 200 times for each of them and queries the movie names table. Every iteration, the query changes and collects one more record, just to make sure we don’t shoot the same query over and over again and hit MySQL’s cache.

Server specification: 32 Core AMD Opteron™ Processor 6376 or Intel(R) Xeon(R) CPU E5-2630 v3, 64GB / 32GB RAM, 4 RAID 1s, SSD Drive.

We made sure that there are no other PHP processes running, nor other MySQL queries on that server, so it’s clean.

Also, we executed all queries in reverse order as well, to make sure that there is no impact on whether we executed Mysqli tests before PDO or PDO before MySQLi.

Each of these sets were executed 50 times, and below are the averages.

Let’s look into the results from the script we ran.

Results with PHP 5.2

pdo - fetched rows in 160.90849 seconds

mysqli - fetched rows in 168.45780 seconds

mysql - fetched rows in 172.66147 seconds

As you can see in the results above, PDO was about 4.5% faster than mysqli and 6.8% faster than the old mysql API.

Results with PHP 7.1.3

pdo - fetched rows in 165.05857 seconds

mysqli - fetched rows in 163.58855 seconds

Here you can see that PDO is only 1% faster than mysqli. The old mysql API wasn’t checked as it’s no longer available in PHP 7.

Also, it’s interesting to see that PHP 7.1.3 seems to be a bit slower than PHP 5.2, at least in this specific experiment.

Summary

This article doesn’t argue that you should use one over the other. It’s a first article in a series of tests we are going to perform to determine that. Said that, we believe this is a good start.

Also, please note that we didn’t use the best practice approach which is to access the database with prepared statements. The reason is because the implementation might be a bit different, and not everyone are using prepared statements for all queries. Therefore, we started with the basics and we’ll move on from there.

Code Reference

In case you’re interested in looking into the code we used for this benchmark, here it is:

<?php
set_time_limit(1000);

test_pdo_buffered(100);

test_mysqli_buffered(100);

test_mysql_buffered(100);

function test_mysqli_buffered($size) {
$servername = "localhost";
$username = "test";
$password = "test123";
$dbname = "testing";

$start = microtime(true);

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

for( $i = $size; $i<$size + 200; $i++ ) {
$sql = "SELECT name FROM movies order by name DESC LIMIT ".$i;
$result = $conn->query($sql);
}

$conn->close();

echo "mysqli - fetched rows in " . number_format(microtime(true)-$start, 5)." seconds<br/>";
}

function test_mysql_buffered($size) {
$servername = "localhost";
$username = "test";
$password = "test123";
$dbname = "testing";

$start = microtime(true);

$conn = mysql_connect($servername, $username, $password);
mysql_select_db($dbname);

if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

for( $i = $size; $i<$size + 200; $i++ ) {
$sql = "SELECT name FROM movies order by name DESC LIMIT ".$i;
$result = mysql_query($sql);
}

mysql_close($conn);

echo "mysql - fetched rows in " . number_format(microtime(true)-$start, 5)." seconds<br/>";
}

function test_pdo_buffered($size) {
$servername = "localhost";
$username = "test";
$password = "test123";
$dbname = "testing";

$start = microtime(true);

$db = new PDO("mysql:host=".$servername.";dbname=".$dbname.";charset=utf8",$username,$password);

if (!$db) {
die("Connection failed: " . $conn->connect_error);
}

for( $i = $size; $i<$size + 200; $i++ ) {
$sql = "SELECT name FROM movies order by name DESC LIMIT ".$i;
$db->query($sql);
}

echo "pdo - fetched rows in " . number_format(microtime(true)-$start, 5)." seconds<br/>";
}
?>