A blog comment from Ian makes me wonder if I failed to state clearly what mysqlnd is. See below for his comment. Ian, I am thankful for your blog comment and question! Probably you are not the only reader who needs a little more background knowledge about mysqlnd. Please allow me to answer your question in public and in depth. FAQ: What is mysqlnd, do I need it?.

The acronym “mysqlnd” stands for “MySQL native driver for PHP”. This pretty much says nothing to most people, I guess.

• “native”: Is it written in PHP? No, that would be two slow. As you probably know, PHP itself is a program written in C. Therefore native means C and tightly integrated into PHP on the level of C. This is for sure a good thing as the driver can try to squeeze out the optimum of PHP.
• “driver”: Is it a new programming API, a new PHP extension? No, there are already three (ext/mysql, ext/mysqli, PDO/MySQL) APIs and three extensions, there is no need for a new API or a new extension! mysqlnd is kind of a library that implements the MySQL low-level communication protocol. This library can be used by the existing extensions. Currently ext/mysql and ext/mysqli have been adopted to run both with libmysql and mysqlnd.
• “for PHP”: This is not really new! You are correct. However, mysqlnd is published under the terms of the PHP license . Therefore, unlike with libmysql, there is no need for the FLOSS License Exception to make the license compatible with that of PHP. Some might recall the – partly heated – discussions about the exception and will love to hear about this.

So, what is mysql? For every old-time and hardcore PHP user a single sentence should explain it: mysqlnd is a replacement for libmysql, distributed under the terms of the PHP license and tightly integrated into PHP on the C-level.

Those of you who would not call themselves C-level PHP hackers, should read on.

Terminology: PHP extension, API

I do not often cite Kristian but this observation of him is just spot on and deserves to be repeated as often as possible: PHP acts like the Borgs: assimilate everything and take the best out of it to strengthen your own position. PHP assimilates C-libraries. At it’s hearth PHP is a small language core which can be extended to do any tasks by embedding specialized C-libraries. Typically embedding a C-library into PHP means writing a new PHP extension and exporting some library functions to PHP userland creates a new API (application programming interface).

Being able to assimilate is one of the key factors in the success and adoption of PHP. It makes PHP be very flexible and be ready for all kind of different and future tasks. Plus, it allows advanced users to break out of the (speed) limitations of PHP and go down to the C-level, if need be.

Quite often a new extension goes hand in hand with a new API. Why would you want to write a new PHP extension, if it does not export any functions to the PHP userland? Most of the time this is not desired. If your PHP extension is based on a C-library, you typically implement some wrapper functions (PHP functions) to be able to use the functionality of the C-library from within your PHP scripts.

Browse the function reference in the PHP manual and you will see how many PHP functions are based on a C-library. The libxml functions have even the abbreviation “lib”(library) in their name: the libxml functions belong to the libxml extension which export C-libary functionality from the libxml library to PHP userland.

A brief history of PHP’s MySQL APIs

At some point in the history of PHP, a very important point in the history of MySQL as well, someone decided to hack a PHP extension that exposes some libmysql (MySQL Client Library) C-level functions to PHP userland: ext/mysql was born.

With the birth of ext/mysql a new API (application programming interface) was born as well. The mysql_*-functions got introduced to PHP. The API is very “phpish” and was somewhat abstracted from the C-level functions found in libmysql. Until today this first extension supporting MySQL seens to be one of the most popular one, although using ext/mysqli is recommended, because ext/mysqli features all functionality offered by MySQL 4.1+, unlike ext/mysql does.

A few years later, with the introduction of PHP 5, ext/mysqli got developed. It is a second PHP extension using the libmysql. And it is a new API which provides the mysqli_*-functions. The reasons for developing yet another PHP extension – ext/mysqli – using libmysql are still listed in a historical article:

• ext/mysql was difficult to maintain due to many #ifdef in the C code
• ext/mysqli supports all MySQL Server features, unlike ext/mysql
• ext/mysqli has a procedural and OO interface – new OO language features have been one of the hottest topics during the development of PHP 4

However, this is not the end of the story. With PHP 5 a third PHP extension using libmysql has been introduced: PDO/MySQL. PDO is a reaction to the fact that PHP does not have a unified API to connect to databases, unlike, for example, Java has with JDBC. Prior to the introduction of PDO, it has been common practice to use database abstractions written in PHP which tends to result in rather slow code. This itself is not necessarily much of a problem, but like with template engines there has never been kind of a “standard” database abstraction. The existing database abstractions did not only have different APIs but also very different feature sets. Having the choice is great on the one hand. On the other hand it was annoying to deal with so many “standards”. As a database API abstraction on the C-level is faster than one implemented in PHP and there was so much confusion about a lacking standard, PDO got developed.

Lessons to learn

Let’s recap. There are three APIs in PHP that you can use to connect to MySQL:

• PHP 2+ – ext/mysql: the oldest extension and API – please don’t use it any more, it does not support all MySQL features.
• PHP 5+ – ext/mysqli: the “current” extension and API – supports all features of MySQL
• PHP 5+ – PDO/MySQL: an extension and database API abstraction layer introduced with PHP 5

mysqlnd is not a new extension! mysqlnd is it not a new API!

Mysqlnd is neither a new PHP extension nor a new API! mysqlnd is new C-level library code. The mysqlnd library provides almost the same functionality as libmysql does. Both C-libraries implement the MySQL communication protocol and can be used to connect to the MySQL Server.

However, libmysql is a generic C-library with Dual-Licensing. Any C-based program can use it. As PHP is based on C, PHP is using it. mysqlnd is not a generic C-library. mysqlnd is licensed under the PHP license and it is tightly integrated into PHP on the C-level. For example, mysqlnd is using the PHP memory management functions and network streams. Due to the close integration, it is difficult for other C programs but PHP to use the library. Any other C program that tries to use mysqlnd would need to link against large parts of PHP. Maybe this explains what “native” and “for PHP” means.

mysqlnd has been designed as a drop-in replacement for libmysql. PHP extensions that use libmysql to connect to MySQL, can be modified to support both libmysql and mysqlnd. This adoption has been finished for ext/mysql and ext/mysqli. Of course, an extension can only use one C-library at a time: either libmysql or mysqlnd. Which one gets used is decided at compile time, see my other blog posting on how to compile mysqlnd.

Answer to Ian

Ian, your question is:

If I’m already using PDO for database connections (via PDO_MYSQL from PECL), is there any reason I should switch to mysqlnd? Would it be faster or perhaps more secure (I’m a big fan of the prepared statements in PDO for preventing SQL injections)?

First of all, a recap:

• PDO/MySQL is an extension using libmysql on the C-level
• PDO/MySQL is an API introduced with PHP 5
• PDO/MySQL has not yet been modified to run with mysqlnd (at the time of writing)

If you want to try out the new superior offering mysqlnd, you need to switch to ext/mysql or ext/mysqli. At the time of writing PDO/MySQL does not support mysqlnd. That means, you would need to switch from one API to another. I doubt you will want to do that as it implies rewriting your scripts.

I found mysqlnd to be roughly as fast as libmysql. Sometimes it is a little faster, sometimes libmysql seems to be slightly ahead of mysqlnd. There are a few differences between mysqlnd and libmysql. For example, mysqlnd needs to hold no extra copies of rows when fetching data. Therefore it can be a little bit more efficient using memory. However, you must never forget that the database layer is only a small piece in the whole game, if you look at PHP from a higher view and consider the whole web request which includes accepting the HTTP request, starting PHP, parsing PHP code, running PHP, spending a few milliseconds in the database layer itself, spending time transferring data from the database server via the network to PHP, processing the data in PHP and finally serving a response to the web client. Don’t raise that high expectations.

Different libraries offer different functionality. libmysql and mysqlnd offer slightly different features. What those features are is to be answered in future writings. We list them in short on http://dev.mysql.com/downloads/connector/php-mysqlnd/:

• improved persistent connections
• mysqli_fetch_all()
• performance statistics call: mysqli_get_cache_stats(), mysqli_get_client_stats(), mysqli_get_connection_stats()

Some of those features have a certain impact on performance. For example, if you compile ext/mysqli with mysqlnd, Persistent Connections can be made. This feature is not there if you compile ext/mysqli with libmysql. Persistent Connections can give you a little performance boost. When comparing apples and oranges – ext/mysqli @ libmysql without persistent connections and ext/mysqli @ mysqlnd with persistent connections – I found the Dell DVD Store to run some 5% faster, depending on the configuration a little less or a little more than 5%. So much about performance.

You are asking about security. Personally I think it is not a task of the database layer to make your applications more secure! You are responsible for filtering data! Applications are state machines. It must not be possible to get from one state to another using the wrong data. It must not be possible to get from “ask user for data” to “store data”, if data contains anything that might cause harm. Sorry, but it is your fault if getting from one of these states to the other is possible using bogus input. If this is possible, your state machine is broken.

What database layers can do is provide you with mechanisms that are robust and prevent faulty input to be executed or stored. By their nature those mechanisms can be only of a very generic type. Database layers do know nothing about your state machine, database layers do not know if they are used in the web or on the CLI, database layers know nothing about your business rules. Therefore any such mechanism does not free you from the task to define clear rules for state transitions and implement those rules, that is to filter input! Any “security” feature of the database layer can only be seen as a last-level backup that tries to prevent the worst. In the first line, your application is responsible for input filtering and validations.

To answer your question: Prepared Statements are available both with ext/mysqli and PDO/MySQL. No matter how you compile ext/mysqli – using mysqlnd or using libmysql. Though, once again, it makes little sense to move the task of input filtering to the database layer. That’s a poor application design. There can be exceptions to this rule, for example when doing rapid prototyping, but in general it is bad style. BTW, ext/mysql does not support Prepared Statements – as said, stop using it, if you still do and go for ext/mysqli.

Is mysqlnd for you? I tend to say no. Currently it is more of interest for ext/mysql and ext/mysqli users. Those users should try it, play with it and run a quick benchmark. How to tweak mysqlnd, what exactly is new and other questions will be discussed in future articles.

Advantages of mysqlnd

• Easier to compile: no more linking against libmysql
• Easier to compile: no need to have libmysql on the PHP build host
• License: PHP license, no need for FLOSS Exception any more
• Native: uses PHP memory management, supports PHP memory limit
• Native: keeps every row only once in memory, with libmysql you have it twice in memory
• New: keeps a long list of performance related statistics for bottle-neck analysis
• New: persistent connections for ext/mysqli
• Performance: can be faster than libmysql in certain cases
• Future performance: we consider a client-side result set cache (pre-alpha design study in SVN)
• … and much more

PS: And why “driver”? Oh, well, good question. We found it to be a bit better than “library”. It is more consistent with the other “Connectors” and somehow “library” is not correct as well… – finally mysqlnl, MySQL enters the Netherlands?