Performance: PDO vs. MySQLi vs. MySQL ?

[bachx]

Alright guys, I see people recommending prepared statements (PDO/MySQLi) and saying that they are way to go these days.

 

However upon doing a bit more research, I've found that prepared statements, PDO in particular, is lacking in terms of performance, especially using SELECT statements. 

 

Now I'm starting a new project, which is basically a text based game with lots of queries and DB interaction, so I'm really interested in knowing what's the best approach for me.  I was leaning towards PDO but I don't want it crawling my server under heavy load.

 

I appreciate any advices or first hand experiences on this.

[Adam]

It's not always about the performance; PDO was designed to provide a single interface between different database platforms. This may not be a concern for you, but for larger businesses or sites that use multiple platforms it's a huge bonus.

 

Prepared statements are faster than normal queries because they're optimized for repeat queries with different parameters - a query run once will not see the benefit. If prepared statements are not supported for a particular database engine, PDO will emulate the behaviour.

 

The MySQLi vs. MySQL extensions is a heavily disputed topic. MySQLi offers more functionality like an optional object orientated interface, however the results between performance vary on the type of query, set-up of MySQL, etc.

 

It's kind of up to your own initiative to choose the right extension for you.

[bachx]

Thanks for the reply. So if I'm looking for pure performance, MySQL is the way to go? For those that used both PDO and MySQL, is PDO noticeably slower in actual web performance? I can see the advantage of prepared statements with repeated queries, however it's somewhat situational.

 

 

[roopurt18]

I use PDO and PostgreSQL on Linux servers for fairly data intensive processes and analysis and performance has always been acceptable.  The advantage to prepared statements, regardless of PDO or another extension is primarily repeated queries will perform better.  In addition, the arguments will also be correctly escaped by the database wrapper as well; this eliminates your need as the developer to insert mysql_real_escape_string() calls all over the place (at least this is true with PDO/PostgreSQL).

 

It's not always about the performance; PDO was designed to provide a single interface between different database platforms. This may not be a concern for you, but for larger businesses or sites that use multiple platforms it's a huge bonus.

This is only true to some extent.  Once you start using SQL syntax supported by only one flavor you still end up writing if-else or switch statements.  However at least the method names and error handling can be somewhat consistent.