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).