PDO Turbo Button

So, a while back, I did some tests with the mysql, mysqli and PDO extensions. In those tests, I found PDO to be much slower for selects than mysql and mysqli. Half as slow in fact. Santos mentioned these tests in a post about SDO. Wez has pointed out that the mysql API does not use the query cache when using prepared statements. Apparently, under the covers, PDO uses prepared statements for all queries. At least that is what I am taking from this. My tests showed the same speed whether I used the PDO prepared syntax or not.

So, I decided to try Wez’s trick of emulating prepared statements to see how PDO did. The results were interesting. Not sure if these are the “fair comparisons” that Wez wants to see, but I gave it my best shot. (Thank WordPress.com for the crappy font size)

extension         req/s
------------------------
mysqli              164
mysql               162
PDO (Wez Trick)     140
PDO (Wez+prepared)  127
PDO                  88
mysqli (prepared)    86
PDO (prepared)       81

So, this is good news. If I had seen these numbers the first time, I would not have been as shocked. There is still overhead obviously. But, its not nearly as big of a drop off.

To comlete the tests, I tried the same trick with inserts. You can see all the code at the link above. Basically, we do 10,000 inserts into a table with some random data.

extension          time
--------------------------
pdo (extended)     18.17s
mysqli (extended)  18.40s
mysql (extended)   18.46s
pdo (prepared)     25.04s
mysqli (prepared)  36.93s
mysqli             50.43s
mysql              53.09s
pdo                59.96s
pdo (Wez+prepared) 85.23s

I ran this several times. Maybe Wez or someone that works on PDO can shed some light on this. Seems like it would be tough to keep track of when to use this flag and when not to. Of course, most web applications are read heavy, not write heavy. My guess is there is more gain to emulating prepared statements than not.

About these ads

2 Responses to PDO Turbo Button

  1. Brian Moon’s Blog: PDO Turbo Button

  2. Joseph Scott says:

    I ran some basic tests (w/PostgreSQL instead of MySQL) using direct database functions, ADOdb (with and without the extension) and PEAR::DB. I found that ADOdb + extension was just a tiny bit faster that PDO.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: