Is the PDO Library faster than the native MySQL Functions?

12
votes

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?

2
phpmysqlsqlpdo
That is interesting question... I was always using PDO just because of one thing... stored procedures... I haven't checked for speed... but stored procedures are enough for me :P. Stored procedures always increase security of web applications, because if you use them, there is no place for "I forgot mysql_real_escape_string() call" :). – Konrad Borowski
If you're aware of the many other advantages to PDO, but are only concerned about speed, why not just benchmark it and find out if it meets your needs? PDO is superior in so many ways, even if it was a bit slower, it still has the advantage. – Michael Berkowski
PDO is marginally slower than mysql functions. However, what mysql extension doesn't have is prepared statements. Therefore, if you have a lengthy insert, using PDO will be much faster via prepared statements because you'll just send parameters to already parsed query. However, speed should not be the deciding factor here. You should use PDO instead of mysql functions. Also, it's much easier to code with PDO plus it's great to do stuff in 1 line that you'd do in 5 - 10 lines with mysql stuff. – N.B.
PDO is an abstraction library, it would be normal to be a bit slower (even if the difference is not that big), but you should choose it for its other advantages (named parameters, buffered queries, no need for escaping etc) – mishu
Thanks for all your responses! I would have tested the speed myself but I have not had a chance – Ben Carey

2 Answers

11
votes

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 ...

1
votes

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.