Overview
Installing/Configuring
Mysqli
PHP Manual

# The MySQL Native Driver

This section of the manual provides an overview of the MySQL Native Driver.

What is it?

MySQL Native Driver is a replacement for the MySQL Client Library (libmysql). MySQL Native Driver is part of the official PHP 5.3 and PHP 6 branches.

The MySQL database extensions MySQL extension, mysqli and PDO MYSQL all communicate with the MySQL server. In the past, this was done by the extension using the services provided by the MySQL Client Library. The extensions were compiled against the MySQL Client Library in order to use its client-server protocol.

With MySQL Native Driver there is now an alternative, as the MySQL database extensions can be compiled to use MySQL Native Driver instead of the MySQL Client Library.

MySQL Native Driver is written in C as a PHP extension.

What it is not

Although MySQL Native Driver is written as a PHP extension, it is important to note that it does not provide a new API to the PHP programmer. The programmer APIs for MySQL database connectivity are provided by the MySQL extension, mysqli and PDO MYSQL. These extensions can now use the services of MySQL Native Driver to communicate with the MySQL Server. Therefore, you should not think of MySQL Native Driver as an API.

Why use it?

Using the MySQL Native Driver offers a number of advantages over using the MySQL Client Library.

The older MySQL Client Library was written by MySQL AB (now Sun Microsystems, Inc.) and so was released under the MySQL license. This ultimately led to MySQL support being disabled by default in PHP. However, the MySQL Native Driver has been developed as part of the PHP project, and is therefore released under the PHP license. This removes licensing issues that have been problematic in the past.

Also, in the past, you needed to build the MySQL database extensions against a copy of the MySQL Client Library. This typically meant you needed to have MySQL installed on a machine where you were building the PHP source code. Also, when your PHP application was running, the MySQL database extensions would call down to the MySQL Client library file at run time, so the file needed to be installed on your system. With MySQL Native Driver that is no longer the case as it is included as part of the standard distribution. So you do not need MySQL installed in order to build PHP or run PHP database applications.

Because MySQL Native Driver is written as a PHP extension, it is tightly coupled to the workings of PHP. This leads to gains in efficiency, especially when it comes to memory usage, as the driver uses the PHP memory management system. It also supports the PHP memory limit. Using MySQL Native Driver leads to comparable or better performance than using MySQL Client Library, it always ensures the most efficient use of memory. One example of the memory efficiency is the fact that when using the MySQL Client Library, each row is stored in memory twice, whereas with the MySQL Native Driver each row is only stored once in memory.

Special features

MySQL Native Driver also provides some special features not available when the MySQL database extensions use MySQL Client Library. These special features are listed below:

- Improved persistent connections

- The special function mysqli_fetch_all()

- Performance statistics calls: mysqli_get_cache_stats(), mysqli_get_client_stats(), mysqli_get_connection_stats()

The performance statistics facility can prove to be very useful in identifying performance bottlenecks.

MySQL Native Driver also allows for persistent connections when used with the mysqli extension.

Installation on Unix

By default the MySQL database extensions are configured to use MySQL Client Library. In order to use the MySQL Native Driver, PHP needs to be built specifying that the MySQL database extensions are compiled with MySQL Native Driver support. This is done through configuration options prior to building the PHP source code.

For example, to build the MySQL extension, mysqli and PDO MYSQL using the MySQL Native Driver, the following command would be given:

./configure --with-mysql=mysqlnd \
--with-mysqli=mysqlnd \
--with-pdo-mysql=mysqlnd \
[other options]

Installation on Windows

In the official PHP distributions from 5.3 onwards, MySQL Native Driver is enabled by default, so no additional configuration is required to use it. All MySQL database extensions will use MySQL Native Driver in this case.

Using Persistent Connections

The MySQL database extensions do not support persistent connections when used with the MySQL Client Library. However, they do when using MySQL Native Driver. When creating a connection the hostname is prepended with "p:". This is demonstrated by the following code snippet, which creates a new connection using mysqli:

$host="p:localhost";
$port=3306;
$socket="/tmp/mysql.sock";
$user="root";
$password="password";
$dbname="test";
$conn = new mysqli($host, $user, $password, $dbname, $port, $socket)
or die ('Could not connect to the database server' . mysqli_connect_error());

Using mysqli_fetch_all()

If you are using mysqli with the MySQL Native Driver, you have access to the new API call mysqli_fetch_all().

As mysqli_fetch_all() returns all the rows as an array in a single step, it may consume more memory than some similar functions such as mysqli_fetch_array(), which only returns one row at a time from the result set. Further, if you need to iterate over the result set, you will need a looping construct that will further impact performance. For these reasons mysqli_fetch_all() should only be used in those situations where the fetched result set will be sent to another layer for processing.

Using Statistical Data

MySQL Native Driver contains support for gathering statistics on the communication between the client and the server. The statistics gathered are of three main types:

- Client statistics

- Connection statistics

- Zval cache statistics

If you are using the mysqli extension, these statistics can be obtained through three API calls:

- mysqli_get_client_stats()

- mysqli_get_connection_stats()

- mysqli_get_cache_stats()

Accessing Client Statistics

To access client statistics, you need to call mysqli_get_client_stats(). The function call does not require any parameters.

The function returns an associative array that contains the name of the statistic as the key and the statistical data as the value.

Client statistics can also be accessed by calling the phpinfo() function.

Accessing Connection Statistics

To access connection statistics call mysqli_get_connection_stats(). This takes the database connection handle as the parameter.

The function returns an associative array that contains the name of the statistic as the key and the statistical data as the value.

Accessing Zval Cache Statistics

The MySQL Native Driver also collects statistics from its internal Zval cache. These statistics can be accessed by calling mysqli_get_cache_stats().

The Zval cache statistics obtained may lead to a tweaking of php.ini settings related to the Zval cache, resulting in better performance.

Statistics returned by MySQL Native Driver

The following table shows a list of statistics returned by the mysqli_get_client_stats(), mysqli_get_connections_stats() and mysqli_get_cache_stats() functions.

Statistic
bytes_sent
bytes_received
packets_sent
packets_received
protocol_overhead_in
protocol_overhead_out
bytes_received_ok_packet
bytes_received_eof_packet
bytes_received_rset_header_packet
bytes_received_rset_field_meta_packet
bytes_received_rset_row_packet
bytes_received_prepare_response_packet
bytes_received_change_user_packet
packets_sent_command
packets_received_ok
packets_received_eof
packets_received_rset_header
packets_received_rset_field_meta
packets_received_rset_row
packets_received_prepare_response
packets_received_change_user
result_set_queries
non_result_set_queries
no_index_used
bad_index_used
slow_queries
buffered_sets
unbuffered_sets
ps_buffered_sets
ps_unbuffered_sets
flushed_normal_sets
flushed_ps_sets
ps_prepared_never_executed
ps_prepared_once_executed
rows_fetched_from_server_normal
rows_fetched_from_server_ps
rows_buffered_from_client_normal
rows_buffered_from_client_ps
rows_fetched_from_client_normal_buffered
rows_fetched_from_client_normal_unbuffered
rows_fetched_from_client_ps_buffered
rows_fetched_from_client_ps_unbuffered
rows_fetched_from_client_ps_cursor
rows_skipped_normal
rows_skipped_ps
copy_on_write_saved
copy_on_write_performed
command_buffer_too_small
connect_success
connect_failure
connection_reused
reconnect
pconnect_success
active_connections
active_persistent_connections
explicit_close
implicit_close
disconnect_close
in_middle_of_command_close
explicit_free_result
implicit_free_result
explicit_stmt_close
implicit_stmt_close
mem_emalloc_count
mem_emalloc_ammount
mem_ecalloc_count
mem_ecalloc_ammount
mem_erealloc_count
mem_erealloc_ammount
mem_efree_count
mem_malloc_count
mem_malloc_ammount
mem_calloc_count
mem_calloc_ammount
mem_realloc_count
mem_realloc_ammount
mem_free_count
proto_text_fetched_null
proto_text_fetched_bit
proto_text_fetched_tinyint
proto_text_fetched_short
proto_text_fetched_int24
proto_text_fetched_int
proto_text_fetched_bigint
proto_text_fetched_decimal
proto_text_fetched_float
proto_text_fetched_double
proto_text_fetched_date
proto_text_fetched_year
proto_text_fetched_time
proto_text_fetched_datetime
proto_text_fetched_timestamp
proto_text_fetched_string
proto_text_fetched_blob
proto_text_fetched_enum
proto_text_fetched_set
proto_text_fetched_geometry
proto_text_fetched_other
proto_binary_fetched_null
proto_binary_fetched_bit
proto_binary_fetched_tinyint
proto_binary_fetched_short
proto_binary_fetched_int24
proto_binary_fetched_int
proto_binary_fetched_bigint
proto_binary_fetched_decimal
proto_binary_fetched_float
proto_binary_fetched_double
proto_binary_fetched_date
proto_binary_fetched_year
proto_binary_fetched_time
proto_binary_fetched_datetime
proto_binary_fetched_timestamp
proto_binary_fetched_string
proto_binary_fetched_blob
proto_binary_fetched_enum
proto_binary_fetched_set
proto_binary_fetched_geometry
proto_binary_fetched_other

Overview
Installing/Configuring
Mysqli
PHP Manual