Which is faster for Large Data Sets? mysql, mysqli or PDO?

I am working with a rather large database of information now and so开发者_高级运维me queries return quite a hefty amount of information. From what I have read, foreach() is quicker than using while() loops with larger arrays, but that can't be used with mysql functions.

So as a general question, which of the 3 is faster for large databases which feth 1000s of rows with quite a lot of data? mysql, mysqli or PDO?

Thanks!

mysql shouldn't be used since it's outdated and has been superseded by mysqli. As for the differences between mysqli and PDO, consider these:

Reasons to use mysqli

• Should be slightly faster.
• Easier, especially if you're familiar with the mysql extension, since it is quite similar.

Reasons to use PDO

• Much easier to transition to other databases.
• More features (eg. named parameters in prepared statements, object mapping).

MySQLi and PDO will give similar performances, but I believe that MySQLi will be faster than PDO as PDO is more of just a wrapper for it. because PDO tailors to multiple databases, where as MySQLi tailors to one. But that can be where PDO has an advantage over MySQLi as well.

The original MySQL is outdated by MySQLi.

For benchmarks: http://jnrbsn.com/2010/06/mysqli-vs-pdo-benchmarks

I don't expect the database interface to be the bottleneck. I expect the actual database to be the bottleneck.

Be sure to understand indexing, InnoDB vs MyISAM, database normalization / denormalization, "EXPLAIN" queries, and prepared queries (both server-side and client-side). THESE are the issues that affect performance, not the choice of mysqli vs pdo.

Its kinda like saying, which bicycle will be faster in a race? Well... the bike doesn't really matter that much, its the person who is riding the bike that makes the difference. Similarly, it doesn't really matter which database layer you use. What matters is how much you have tuned your query and tweaked your database.

If you use the Zend_Db class as part of Zend Framework, you can easily switch between mysqli and PDO. Zend_Db is an extra abstraction layer. To switch underlying libraries you only need to change 1 parameter. That's all.

This way you can write your code once, and benchmark yourself.