MySQLi vs. PDO (my version)

Sun, Aug 6, 2006 05:45 PM

The recent post by SantosJ reminded me of a write up I did for the dealnews developer page. His post dealt with the good and bad of coding with PDO. For me, it was about speed, not ease of use. I will write any freaky code to save a cycle or two. In my tests (code is on the linked page), I found that PDO was slower than mysqli in every test except prepared inserts. Since the bulk of our application (and I suspect, most web apps) is read bound, that is not a big win for me. I was happy to see however that mysqli was in no way slower than the trusty old mysql extension. Good job to whomever did that extension.

9 comments

Adam Trachtenberg Says:

That was Georg Richter.

Lukas Says:

Since I did not see a way to add a comment to the original post I will leave my comments here.

Ok so using ext/mysql means you have to use the old authentication protocol. I recommend to use mysqli if you are on phh5 not only for that reason.

I do agree that the mysqli prepared API is needlessly cumbersome and inconsistent with the non prepared API.

About prepared statements: The idea is that the server gets the structure of the query separated from the values so that the database can already generate a generic query plan for any kind of values (actually most RDBMS wait for the first set of values to really generate the query plan). This means you cannot omit tables etc. In more recent versions of MySQL you can however use placeholders in LIMIT clauses.

Hans Says:

I don't understand this comparison. You are comparing the performance & featureset of a database abstraction layer with that of the native database API? I'm sure that no one expected PDO to compare in performance to the native DB API. And I'm sure no one expected PDO to have an API as rich as the native DB API either. The thing about database abstraction (or any abstraction, really) is that you are limited by the simplest API that you are planning to support. Those that choose to use PDO are doing so because they value the ability to write code that supports more than a single database.

IMO, the better question is whether PDO is faster than other DBAL solutions -- e.g. DB, MDB, ADOdb, etc. That would be a more interesting comparison (and would be surprising, if it's not faster). Otherwise, it sounds like trying to compare a sports car to a family sedan/saloon. Yes, the sports car is faster and handles better -- yet somehow there is still a market for sedans.

-Hans

Wez Furlong Says:

I'd be interested to see how those numbers work out if you take http://netevil.org/node.php?uuid=444a6017-0548-2459-2943-44a601714d58 into account.

doughboy Says:

Sorry about getting these comments up so slow. I was unaware they were awaiting moderation.

doughboy Says:

Hans,

The problem is that performance is not brought up most of the time when talking about cool new features like PDO. At OSCON, PDO got a lot of exposure. In fact, PDO with PostgreSQL was used in Rasmus' talk that discussed debugging a PHP app to make it faster. He did not cover switching from PDO to the navite PostgreSQL API. Of course, I have not tested those either. For all I know, Rasmus could have tested it and found it faster. So, while someone that understands the internals of PHP might know that PDO is going to be slower, the laymen that just started using PHP will not. They will use PDO because that is getting all the press. And two years later when they are having issues with their server and need to optimize things, they still may not realize that PDO is much slower than the mysqli extension.

I would not even bother measuring PDO vs. other user land abstraction systems. PDO would have to have been written badly to not out perform them.

chiggins Says:

<blockquote>In my tests (code is on the linked page), I found that PDO was slower than mysqli in every test except prepared inserts. Since the bulk of our application (and I suspect, most web apps) is read bound, that is not a big win for me.</blockquote>

Typically, if I end up in a situation where the read speed difference between the two is an important enough factor for it to be a big deal, then I'm not caching at the right places (check out Memcache, it's a lifesaver). It would have to be a lot slower, I mean a <i>lot</i> slower, to overcome the advantages of code portability to me. But that's just me.

doughboy Says:

<blockquote>(check out Memcache, it’s a lifesaver).</blockquote>

Heh, you really should read some more of my blog. =)

Tony Landis Says:

Thanks Brian... for those interested, I did a benchmark of PDO and the ADOdb library on my site...

Comments are disabled for this post.