PHP MySQL developers are confronted with three PHP MySQL APIs. That is confusing. Since mid 2012 the PHP manual suggests not to use the oldest and original PHP MySQL extension any more to cure the situation. Please, swap out ext/mysql in favour of either ext/mysqli or PDO_MySQL. Why to upgrade is below. Read on, if you are a PHP beginner, your PHP application is still using mysql_* functions of ext/mysql or you are a PHP trainer.

As a PHP consumer, tease your software vendors by asking whether they use the state of the art APIs! Here is a cut&paste test question to ask your vendor: do you support secure SSL connections to MySQL?

History and features

PHP and MySQL go hand in hand. The original MySQL API (ext/mysql) is around since PHP 2.0. Many web sites still show examples of the original MySQL API (ext/mysql). This is amazing considering the feature comparison of the three PHP MySQL APIs. By the way, do you know this Quickstart guide on ext/mysqli that is in the PHP manual?

The above is almost identical to what you find in the PHP manual at http://www.php.net/manual/en/mysqlinfo.api.choosing.php with minor additions to elaborte and make the points. If you are using the original ext/mysql API, you find yourself in the red zone! Get rid of the weak points in your application.

Soft features

Humans have hard skills and soft skills. Let’s see if that works for APIs too – a personal list of soft features.

Performance considerations

All three PHP MySQL APIs are tiny C wrappers around the underlying C client-server library. It is the underlying C client-server library that does most of the work.

For the same command executed using the same feature you can expect all APIs to show the same performance. How you wrap the C library in a C PHP extension does not matter. It is the features exposed to the users that matter. Different features have different performance characteristics.

Before doing endless profiling sessions ask yourself how much time your web applications spends in the API itself. I am not talking about the accumulated wall clock time of query or connect functions. I ask you to measure pure API times without query and network times!

Features is what counts for performance. The matrix of performance relevant features makes ext/mysql look poor.

Performance: asynchronous, non-blocking

There is no way to run a non-blocking query using ext/mysql. You cannot fire off a slow running query at the beginning of your script, do some work and fetch results once the server has computed them. Instead you are forced to wait for the results. For example, you can query an ad server without having to wait for it. In 2008 a conference talk was given on: How mysqlnd async queries help you with sharding!. Here is the corresponding success story from 2012: Asyncronous Shard Queries in PHP using mysqlnd enabling the feed load 10 times faster.

Performance: servers-side prepared statements

A non-prepared statement is parsed and interpreted by the server every time you execute it. SQL and data is sent to the server in one chunk. A prepared statement is first parsed and prepared on the server before it can be executed multiple times with different parameters without being reparsed again. SQL and data sent to the server seperatedly. Upon repeated execution only the parameter value is transferred, thus bandwith is saved.

Whether using a prepared statement gives a performance penalty or performance win depends on the case. Below is an example that showed a 25% higher query rate using prepared statements on my aged test box. If you don’t know about this feature, which is unavailable with ext/mysql, read this quickstart. Performance is about features being available in the API and smart, trained use of the features.

$link = new mysqli("localhost", "root", "", "test", NULL, "/tmp/mysql57.sock");
$link->query("DROP TABLE IF EXISTS test");
$link->query("CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY, note VARCHAR(255))");

$time = 100;
$note = 'How fast are prepared statements?';

$num_queries = 0;
$start = microtime(true);
do {
  $num_queries++;
  $my_note = $link->real_escape_string(sprintf("%s %d", $note, $num_queries));
  $res = $link->query("INSERT INTO test(note) VALUES ('" . $my_note . "')");
  $runtime = microtime(true) - $start;
} while ($runtime < $time);

printf("Non-prepared: %d qps\n", $num_queries / $time);

$num_queries = 0;
$start = microtime(true);
$stmt = $link->prepare("INSERT INTO test(note) VALUES (?)");
$my_note = NULL;
$stmt->bind_param("s", $my_note);
do {
  $num_queries++;
  $my_note = sprintf("%s %d", $note, $num_queries);
  $stmt->execute();
  $runtime = microtime(true) - $start;
} while ($runtime < $time);

printf("Prepared: %d qps\n", $num_queries / $time);

Performance: multiple statements

What if you could fire multiple statements with just one question to reduce client-server round-trips? Using Multiple Statements it is possible, unless you are using ext/mysql, which cuts you off from this possible optimization.

$link = new mysqli("localhost", "root", "", "test", NULL, "/tmp/mysql57.sock");
$link->multi_query("
  DROP TABLE IF EXISTS test ;
  CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY, note VARCHAR(255)) ;
  INSERT INTO test(id) VALUES ('multi statement');
  SELECT * FROM test;
  SELECT id FROM test; ");

do {
  if ($res = $link->store_result()) {
    var_dump($res->fetch_all(MYSQLI_ASSOC));
  }  
} while ($link->more_results() && $link->next_result());

Performance: summary

API internals don’t matter much, they hardly contribute to overall run time. Reducing SQL runtimes is most important followed by querying MySQL in the most efficient manner. The different options for querying MySQL with different performance characteristics that can be used for optimizations on a case-by-case basis. A certain old API cuts you off from some potential performance gains…

Security related features

Security has a lot to do with how you use a given tool and which tools you are given.

Security: Encryption/SSL

MySQL supports secure connections between clients and MySQL clients and the server using the Secure Sockets Layer (SSL) protocol. Both ext/mysqli and PDO_MySQL feature SSL connections. Thus, PHP MySQL users can use SSL for their business.

But, ext/mysql will not let you set the configuration settings required for an SSL encrypted connection. Feel free to use "the world’s foremost network protocol analyzer" (Wireshark) to sniff on any ext/mysql connection. The graphical user interface of Wireshark is simple to use. Good luck stealing information.

Security: SQL injection

There are many ways to fight SQL injection. Please, consider watching the web presentation Top 10 MySQL Tips and Mistakes for PHP Developers if you are new to the topic.

It is certainly possible to develop a bullet-proof web application on top of ext/mysql but it requires skill. The below example is taken from the article Not only SQL injection: I don’t trust you!. Do you see the denial-of-service attack? The answer is in the link. It is not only about charsets. Do you notice the charset pitfall in the API?

$query = "SELECT name, rating FROM movies WHERE rating > " .
  mysql_escape_string($_GET['rating']);
mysql_query($query, $link);

For an untrained person, a beginner, the simplest solution to the problem would be to use (server-side) prepared statements. A feature available with all PHP MySQL APIs but ext/mysql.

Security: Persistent connections

Persistent connections have been reason for vivid discussions ever since. A persistent connection is not closed at the end of a script but kept open for reuse in a future script run. A persistent connection keeps its state between use, as the name says. This very property has caused lots of confusion ever since. People have been surprised to be confronted with a connection that has open transactions or SQL user variables set. Many considered this a pitfall.

Imagine the DBA notices a PHP MySQL account has been granted to many rights, or, in the worst case has been compromised. The DBA changes the MySQL account. Your web application has many persistent connections open. The open connections are unaffected from the account changes. PHP continues to issue queries that you tried to prevent until all persistent connections have been closed.

The ext/mysqli API is the only one that is not flawed by default. The mysqli extension does flush the state of a persistent connections and reauthenticate the user (COM_CHANGE_USER) before reusing the persistent connection. Thus, no pitfalls around state, and no delayed recognition of account changes. Strictly speaking ext/mysqli gives you no persistent connection but a pooled one. However, those who know what they do can go back to the old behaviour. The price paid for "no pitfall" persistent connections is performance (german speaking readers can find details here and here).

Cosiness and pitfall factor

Upon closer look it turned out that performance and security are more hard facts than soft ones. Even cosiness and pitfall factor have a touch of hard facts that cannot be ignored.

Undoubtfully the most powerful PHP MySQL API is ext/mysqli. But, it is also the most complex one, which gives a minus on the cosiness factor. When it was introduced it served as a pioneer for dual procedural and object-oriented interfaces. The procedural interface was preserved to make migrating from ext/mysql easy. PDO came a tad later and got a more comprehensive, though less powerful, pure OOP interface that many would rate higher for cosiness.

The original PHP MySQL API (ext/mysql) lacks on features and API calls – cosy! Seriously, it has some neat looking stuff in it but watch out for traps…

$res = mysql_query("SELECT 1"); 
var_dump(mysql_fetch_assoc($res));

This is a complete script to query MySQL. It is no mistake that you see no call to any kind of connect function. Here is the proof using PHP 5.6:

nixnutz@linux-0v4u:~/git/php-src> sapi/cli/php 
  -d mysql.default_host="localhost" 
  -d mysql.default_user="root" 
  -d mysql.default_socket="/tmp/mysql57.sock" 
  -r '
$res = mysql_query("SELECT 1"); var_dump(mysql_fetch_assoc($res));'

array(1) {
  [1]=>
  string(1) "1"
}

Great stuff, thumbs up? Next example. Let’s assume the implicit connect happens somewhere deep, deep in your startup routine. Then, in the main part of your script you explicitly open a connection with mysql_connect(). You get a resource identifier for your connection and you use it with mysql_query(). Guess what, you are using the connection that was opened somewhere deep, deep in your startup routine.

nixnutz@linux-0v4u:~/git/php-src> sapi/cli/php 
  -d mysql.default_host="localhost" 
  -d mysql.default_user="root" 
  -d mysql.default_socket="/tmp/mysql57.sock" 
  -r '
function startup() { 
  mysql_query("SET @a = 1"); 
} 
startup(); 

$link = mysql_connect(); 
$res = mysql_query("SELECT @a", $link); 
var_dump(mysql_fetch_assoc($res));'

array(1) {
  ["@a"]=>
  string(1) "1"
}

It is not mandatory to pass a connection identifier to mysql_query(). The function will use the last opened connection – cosy! Particularily older code makes use of this feature. Assume you modern() stuff to your old application. You believe to do everything right. You tell mysql_connect() to open a new connection as you do not want to be fooled. For this you pass true as the fourth argument to the function. Then, you ensure the connection identifier is used with mysql_query(). Everything is beautifully encapsulated in a function – what should go wrong?

nixnutz@linux-0v4u:~/git/php-src> sapi/cli/php 
  -d mysql.default_host="localhost" 
  -d mysql.default_user="root" 
  -d mysql.default_socket="/tmp/mysql57.sock" 
  -r '
function startup() { 
  mysql_query("SET @a = 1"); 
} 
function modern() { 
  $link = mysql_connect(NULL, NULL, NULL, true); 
  mysql_query("SET @a=2", $link); 
} 
startup(); 
$link = mysql_connect(); 
modern(); 
$res = mysql_query("SELECT @a"); 
var_dump(mysql_fetch_assoc($res));'

array(1) {
  ["@a"]=>
  string(1) "2"
}

Well, your function has side effects. It has accidently made the main routine use the connection established within modern(). No, closing the connection in modern() is no solution. Maybe some cosy looking features contribute to the pitfall factor…

You deserve the best

Do yourself a favour and choose the best possible API for your PHP MySQL applications. Either use ext/mysqli or PDO_MySQL. The original MySQL API will not allow you to go beyond the MySQL 4.1 feature set – quite limiting.

Happy hacking!

@Ulf_Wendel