After reading this article on Nettuts, I was curious how PDO would compare to MySQLi in a real-world scenario, perfomance-wise. So I created a benchmark to dispell the myths surrounding this dispute. MySQLi is percieved to be the better performer, since it’s the official extension supported by Oracle. In the current post we’ll test each database driver.

The Setup

For any test result to be relevant we need:

  • an isolated test environment
  • to run multiple rounds of the test
  • to specify the software version used

The software I used was: PHP 5.3.6, MySQL 5.1.58 on Ubuntu 11.10 with Linux kernel 3.0.0

I tested the performance of the four most important SQL operations: INSERT, SELECT, UPDATE and DELETE. These four operations are enough to determine the raw performance of each driver.
The test suite consists of running these queries 100000 times (100k) and 1000000 times (1M) for each database driver.

To make sure that the test is not affected by other applications (such as a sudden cron job), I ran the tests from the CLI, with other daemons shut down.

The SQL schema for the database is:

Groups table

id_group (PK)
title

Users table

id_user (PK)
id_group (FK)
first_name
fam_name
email
pass

Not very complicated. I ran the queries only on users table. In both cases I used prepared statements (with named parameters for PDO).

Results

Here are the results of the tests. Lower values are better, of course.

PDO results for 100k queries

query time in seconds
insert 13.079864025116
select 19.150141954422
update 16.263291120529
delete 14.891561985016

MySQLi results for 100k queries

query time in seconds
insert 19.463269948959
select 27.461564064026
update 22.705169916153
delete 21.583913087845

PDO results for 1M queries

query time in seconds
insert 133.66887807846
select 197.49514484406
update 164.97567796707
delete 150.77637290955

MySQLi results for 1M queries

query time in seconds
insert 202.65716481209
select 283.04056501389
update 231.89973783493
delete 232.20053887367

The results are quite surprising: PDO performs better than MySQLi in all tested cases. You can find the source of these tests on Github.

Apart from better performance, PDO has proper exception handling, a better object-oriented interface and supports binding of entire arrays. Now you have no reason not to use PDO.

Erratum

This benchmark is flawed, as PDO does escaping/preparing entirely on the client-side, while MySQLi uses the mysql protocol for that (thus network IO).