wooptoo i do webdev

PDO vs MySQLi performance comparison

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.

Tags

10 Comments

  1. Bob

    I’m assuming you ran the MySQLi test right after the PDO one?

    Did you remember to truncate the tables before running the MySQLi tests, so it was starting on a fresh database? Otherwise I could see the subsequent tests you run on it going slower because there’s more data in it.

  2. Radu Potop

    The queries for each test were run in this order: INSERT, SELECT, UPDATE and DELETE. So after a test is complete the table would be empty.

  4. Keeton

    Thanks for taking the time for this. I was surprised by the results as well. What kind of hardware did you use for testing?

  6. Tim

    This comparison is absolutely horrible. PDO emulates prepared statements by string manipulation entirely on the client side. MySQLi actually uses the MYSQL API to prepare the statements — which means network traffic.

    You’re also preparing a statement, and destroying it, every single time. A realistic scenario would prepare a statement, execute it 10,000 times with changing data, then destroy it.

    This comparison is utterly pointless and invalid.

  8. Radu Potop

    @TIM:

    I knew PDO emulates prepares for some drivers that do not support them natively, but I wasn’t expecting it to emulate by default for MySQL. So ATTR_EMULATE_PREPARES needs to be explicitly set to false. My fault.

    There have been a lot of comments on preparing the statements at each iteration. I did this on purpose because I wanted to test the raw performance on a high volume of variating queries (yes, I actually need this for a db intensive application). I know that it doesn’t fit everyone’s scenario. Maybe another test with only one prepare/run would be welcome.

    So I guess this post needs a follow up, with better testing methods.

Leave a comment