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.