I have read several questions regarding this but I fear they may be out of date as newer versions of the PDO libraries have been released since these questions were answered.
I have written a MySQL class that builds queries and escapes parameters, and then returns results based on the query. Currently this class is using the built-in mysql functions.
I am well aware of the advantages of using the PDO Library, e.g. it is compatible with other databases, stored procedures are easier to execute etc... However, what I would like to know is simply; is using the PDO Library faster then using the mysql built-in functions?
I have just written the equivalent class for MsSQL, so rewriting it to work with all databases would not take me long at all. Is it worth it or is the PDO library slower?
Performance. While both PDO and MySQLi are quite fast, MySQLi performs insignificantly faster in benchmarks - ~2.5% for non-prepared statements, and ~6.5% for prepared ones. Still, the native MySQL extension is even faster than both of these.
The main advantage of PDO over MySQLi is in the database support. PDO supports 12 different database types, in opposition to MySQLi, which supports MySQL only. When you have to switch your project to use another database, PDO makes the process simpler.
MySQLi is a replacement for the mysql functions, with object-oriented and procedural versions. It has support for prepared statements. PDO (PHP Data Objects) is a general database abstraction layer with support for MySQL among many other databases.
There is no difference in security. The main difference between PDO and Mysqli is that PDO supports various databases and mysqli supports only MySQL. MySQLi is also a bit faster. PDO supports 12 different drivers, opposed to MySQLi, which supports MySQL only.
I found PDO in many situation/projects to be even faster than the more native modules.
Mainly because many patterns/building blocks in a "PDO-application" require less php script driven code and more code is executed in the compiled extension and there is a speed penalty when doing things in the script. Simple, synthetic tests without data and error handling often do not cover this part, which is why (amongst other problems like e.g. measuring inaccuracies) I think "10000x SELECT x FROM foo took 10ms longer" conclusions are missing the point more often than not .
I can't provide you with solid benchmarks and the outcome depends on how the surrounding application handles the data but even synthetic tests usually only show differences so negligible that you better spend your time on optimizing your queries, the MySQL server, the network, ... instead of worrying about PDO's raw performance. Let alone security and error handling ...
My observation is that PDO seems to be less tolerate of many consecutive connections - that is connections being created in a loop. I know this is bad practice it the first place. When I was using mysql_* my looped queries seemed to be reasonably fast. However when I switched to PDO I noticed much longer response times for these types of queries.
TL;DR; - If you switch to PDO and you call queries in a PHP loop you may need to rewrite the application to call one single query rather than many consecutive queries.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With