Has anyone looked into the diferences between the diferent MySQL extensions?
The mysqli and PDO are more object oriented and mysqli is object oriented but closed around mysql logic...
I haven't wasted mutch time looking into them, in mysql extension basic of php I have used mysql_real_escape_string to parse input that whent into the sql query. I have this logic inside a class witch I think will ease the port to a diferent extension.
But the main question is, do we get speed, memory gain? What are the advantages of this new extensions?
Manuel Lemos - 2011-07-15 10:22:13 - In reply to message 1 from Fernando André
The only MySQL extension did not seem to be updated to support features available in newer MySQL server versions.
Anyway, if people should switch to a better MySQL extension, they should ignore mysqli and PDO and go right to MySQLND, which is a native PHP optimized driver developed by MySQL people having specifically PHP in mind.
Artur Graniszewski - 2011-07-15 15:40:15 - In reply to message 2 from Manuel Lemos
What? Tell me if I'm wrong, but MySQLnd is not a standalone library as MySQLi or MySQL or PDO. It's a internal db driver used by different MySQL* layers (like PDO, etc). You can link it from your own extensions.
What's more. In hosting company I worked for it is enabled by default (because of the performance reasons), and none of the existing PHP scripts of the company's customers has been broken (yet they use old MySQL extensions/PDO/mysqli)
Mustafa Rampurawala - 2011-07-15 15:46:49 - In reply to message 1 from Fernando André
I too have got a bird eye look on the mysqli extention. But, i think its nice to adopt it in the future code development, and try updating the old code on the way. Here i wiould like to bring 2 things into notice.
First, for those who just want to upgrade from mysql to mysqli to make their script compatible, nearly all the mysql methods are supported by mysqli. The difference is very small like, mysql_connect is now mysqli_connect (here, 1 can now pass the database name too in the same connect statement, or if preferred can do it the same old way with mysqli_select_db) and mysql_query becomes mysqli_query, except for the fact that the $link comes before $query and now $link is not optional.
Second, for those who want to reap the benefits of the mysqli extention, there are many good things to write, but that can be done in its own post. here will only like to highlight that you no longer need mysql_real_escape_string now, just prepare the statement and then pass values with there type and mysqli will take care of the rest. Also, as you asked, the speed difference and resource handling efficiency improvement can be seen here, as the database overhead is greatly reduced in prepared statements, especially if you need to insert/update many records in single batch. Not tested, but officially its said to be 40 times faster.
Manuel Lemos - 2011-07-16 04:30:04 - In reply to message 3 from Artur Graniszewski
Sorry, I expressed myself in a misleading way.
What I thought but did not say, is that there should be a new PHP MySQL API to access MySQL database asynchronously, ala Node.js. That could be used to make PHP run faster by doing some work while queries are executed in parallel.
Manuel Lemos - 2011-07-16 09:20:50 - In reply to message 6 from Fernando André
We all know the pcntl extension but that is not what we are talking about when referring to asynchronous programming.
That would make a more efficient use of CPU and I/O resources. In Node.js, you can do server-side asynchronous programming as a natural thing.
In PHP, there is some support to access the network and files asynchronously, but nothing for accessing databases asynchronously, although Jamie commented it is possible to perform some tricks using mysqlnd that he ended up not revealing, probably because it would require disclosing some business secrets of his company.