Most all the current documentation out there encourages PHP developers to use the PDO API. Though I’m still wondering if this is the best option for most developers, as the majority of data driven websites are ran from a MySQL data source.
In most of the arguments I’ve heard in favor of PDO, they’re in comparison to the old MySQL API, which isn’t a fair comparison as it almost seems they’re intentionally forgetting to mention the newer MySQLi. Both PDO and MySQLi offer an object oriented interface and both support all the most important features like prepared statements and stored procedures. Further, from my understanding MySQLi is better optimized for a MySQL database, offering better performance, an easier to use interface as well as ability to send multiples statements in a single call.
I understand that if you ever want to switch DB systems, PDO simplifies this process. But I don’t see why this is such a huge selling point of PDO. Switching DB systems is something that’s very rarely done for an application. Further, if you are using proper abstraction of functionality as with MVC architecture, converting to a new DB system doesn’t seem to be that major of an overhaul.
If your current system is using MySQL and you don’t foresee an immediate need for change, is there any real reason to use PDO over MySQLi?
@brandonBuster, can you elaborate more on the multiple statements? Maybe provide an example of your mysqli code that shows what you mean? As I did a bit of digging and I can’t find any source that says you can’t do multiple statements in PDO.
Okay, that is interesting (kind of wish the Most was a link to more detail), but from the sources I found, it seems you can do just about anything (in regards to multiple statements) if you use it in a transaction and provided none of them fail. Not as convenient, but it does help if you ever need to not commit your data access.
Thanks for pointing out my error about PDO’s “complete” inability to perform multiple statements. Especially, thanks to cpradio for the info on how to use those capabilities it does have.
About these interfaces being equally optimized for MySQL, they do use the same driver, but PDO sits behind an added layer of abstraction. I’m not sure how much that data abstraction layer may cost much in overhead, but imagine it is part of the reason MySQLi has consistently been said to outperform PDO in all the articles I’ve read.
PDO had issues with prepared statements in the past, where it did not handle prepared statements like it should, but I am certain this was fixed with the switch to the new mysql driver.
So if the posts/articles you found with the speed issues are a few years old, I am not sure how valid they are now. Assuming you are running 5.3< of course.
Note: I believe PDO still have a problem with stored procedures if they should return information. It might be fixed, but I have not retested this. So if you depend on that, you might stick with mysqli.
Okay, so I decided to search for the performance implications myself, and find that most articles attribute PDO to be 2.5 to 6.5 percent slower. I don’t know about you, but that is insignificant in 90% of the applications I develop. There is actually only 1 application that would have prevented me from using PDO that I was part of in the last 12 months (if it were a PHP project and not a .NET project) as the time savings of using mysqli would have resulted in actual seconds not just milliseconds and my rule of thumb is to only measure it seconds (something that a user would actually be able to notice).
Granted there is a lot of controversy in the results I found so in the end I would want to run my own benchmarks before committing to PDO over MySQLi. Below are just a few of the comparison sites I found. http://we-love-php.blogspot.com/2012/07/mysql-or-mysqli-or-pdo.html
To be fair, PDOs implementation of named parameters (and that’s what they are, they get converted by PDO into question marks before being sent to mysql) isn’t great because it doesn’t allow you to use the identifier twice, it’ll only replace the first occurnce. Defeats the purpose really, may as well just use question marks.
That 2.5% is a bit of a red herring. Keep in mind it’s only 2.5% of the PHP time, the time the query takes to run will be identical on both. The time PDO or MySQLi take to send the query then convert the the response from the database to an Object In PHP is what’s being measured, and that is a fraction of the total time; most of the delay is due to the database. It is not the case that a query that takes 1s to run in MySQLi will take 1.025s in PDO and a 10s MySQLi query will take 10.25s in PDO.
I guess my point (which was stated later) was there was too much inconsistency in the results I found regarding performance and the actual cost between the two, that if I were put in the position to decide MySQLi or PDO, I’d run my own tests based on what my application needed to do against the datastore to see if PDO or MySQLi would raise a red flag.
However, if my own results indicated a 2.5% to 6.5% slowness (per query) in using PDO and I knew that most pages would only result in up to 8 queries, I’d toss a coin, as the result at the end of the day won’t be much of a difference.
I agree, the PHP execution of PDO versus MySQLi is where the performance loss/gain will be found, your query regardless of which process you use to execute it, will and should run identically against your database (thus removing the cost of network/mysql – with the exception of running buffered/unbuffered; that could result in cost from MySQL and your network). This is more of a reason why I think the performance measurements are not entirely reliable. The code execution will cost some, but to say it could cost 300% (as some articles claimed) seems a bit ridiculous to me. If I were to measure such, I’d want to do it by altering PHP’s source to write logs during the PDO and MySQLi executions so I could get down to the bare bones. Otherwise, I’d end up running a test, restarting MySQL, run the next test (all on a local machine) to try and remove abnormalities (but even that isn’t really good enough).
I think it should be mentioned the performance losses reported in using PDO were 2.5% for non prepared statements and 6.5% for prepared statements. That said, the 6.5% should be the main figure a developer should consider, as prepared statements are one of the main reasons to use a modern database API like MySQLi or PDO. While a 6.5% loss in processing speed might not make much a difference, I do believe it is a lot to give up voluntarily, without good reason.
Given only the information in this thread, from purported performance losses, to the limited benefits of named parameters, to PDO’s requiring multiple statements be wrapped in a transaction, what I’m still taking away is that if you know you are using a MySQL database, there’s no real reason to use PDO.