PHP MySQL Extensions

From wiki.mikejung.biz
Jump to: navigation, search

Overview

PHP has a few different extension that can be used for accessing a MySQL database. Each extension can use whatever API it wants, and the extension can either expose the API to the programmer, or it can choose not to. Extensions may use a certain API, but the two are not the same thing. Some of these extensions might offer more API access to the programmer, allowing them to exploit more cool features. It's important to learn about each extension so that you use the right one in production.

"APIs can be procedural or object-oriented. With a procedural API you call functions to carry out tasks, with the object-oriented API you instantiate classes and then call methods on the resulting objects. Of the two the latter is usually the preferred interface, as it is more modern and leads to better organized code."

A connector is a piece of software that MySQL provides for lots of different languages, like PHP. This software is what allows other APIs to talk to MySQL. That's basically all it does, pretty much the ears of MySQL.

There can be many different drivers in a connector package, so the driver is very focused on the type of database it wants to talk to, like MySQL, or SQlite.

What API is the best?

PHP recommends using the MySQLi or PDO_MySQL extension, the old MySQL extension is not being updated any longer. Performance wise, the extensions all perform about the same, the difference is whether or not you want an object oriented, or procedural interface, if you want procedural then you want to use mysqli, if you want to use object oriented then you would want to use PDO_MySQL.

The mysqli extension API supports non-blocking, asynchronous queries with mysqlnd, PDO_MySQL does not support this functionality. If you look at the chart located at the link below you can see that in most cases, mysqli offers more than PDO_MySQL.

mysqlnd versus libmysqlclient

"The mysqli, PDO_MySQL and mysql PHP extensions are lightweight wrappers on top of a C client library." These extensions can use the mysqlnd library, or they can use the libmysqlclient library. You can choose which library to use when you compile PHP.

Since PHP 5.3, the mysqlnd library has been included in the main PHP distribution. Some notable features of mysqlnd includes "lazy connections" and query caching. Because of how useful these two features are, it's suggested that you use the built in mysqlnd library instead of libmysqlclient.

MySQLnd also supports performance statistics, read / write splitting, load balancing and asynchronous queries

How to compile PHP with mysqlnd library

You can configure PHP to use the mysqlnd library by configuring PHP with the following options. It is suggested that you use mysqlnd instead of the libmysqlclient library since mysqlnd offers query caching and more features.

./configure --with-mysqli=mysqlnd --with-pdo-mysql=mysqlnd --with-mysql=mysqlnd

mysqlnd plugins

mysqlnd-memcache

MySQLnd has a plugin which allows for translating SQL into requests for the MySQL InnoDB Memcached Daemon Plugin. This plugin provides access to the data stored in MySQL's InnoDB tables, and any select queries can then be sent to Memcached so that repeat queries can come from RAM instead of MySQL. By doing this you remove a lot of strain on MySQL.

The MySQL mysqlnd-memcache REQUIRES the InnoDB Memcached Daemon Plugin to be active for MySQL, otherwise the plugin won't work as expected. You can use other extensions if you want to directly interact with memcached, these are called memcache and memcached.

mysqlnd-memcache requires PHP 5.4+ as well as the memcached extension that is newer than version 2.0. The plugin also requires MySQL 5.6 or later with the InnoDB Memcached Daemon Plugin active.

You can enable, or disable mysqlnd_memcache by modifying the value below, this can be found in the main php.ini file. The default value is 1, which means it's enabled.

mysqlnd_memcache.enable=1