H.1 New Features

The improved MySQL library adds new features, remove old features, improves performance, and offers more flexibility compared to the standard MySQL library. These changes include:

Prepared statements and parameter binding

This feature allows you to speed-up the processing of repeated queries that perform the same function. For example, it's a fast way to insert hundreds or thousands of rows into the same table.

Compressed and encrypted connections

You now have more flexibility over how you communicate between your web and database servers. By using compression, you can reduce the amount of data transferred between the servers; this will speed up your communications if the servers are installed on different machines that communicate over a network. With SSL encryption, you can secure a connection between your servers; this is of benefit only if the connection between your web and database server is susceptible to the security problems discussed in Chapter 11.

Object-oriented methods

You can now choose whether to use the traditional procedural style to call the functions (as described in Chapter 6) or to use an object-oriented style that's similar to that used by PEAR DB.

Transaction control

You can turn MySQL's autocommit feature on or off, and also call functions that start, commit, and roll back transactions. Transactions are discussed in detail in Chapter 15.

Replication and distribution support

For high-end applications, MySQL databases can now be replicated across many machines. In this configuration, a master server manages slave servers, and the new library supports functions that allow you to control how the master and slaves are used to evaluate queries. We don't discuss replication in this book.


The profiler tracks all MySQL function calls and writes information about these to the stderr device on Unix servers, to a file, or to a Unix network socket. The information written includes the file and line number of each MySQL function that's called, the execution time of the function, the output of the EXPLAIN statement for that query, and any error messages or warnings. The EXPLAIN statement is discussed briefly in Chapter 15.

Code cleanups and optimizations

Functions have been removed, changed, and added in an effort to improve how scripts can access the MySQL server. For example, the mysqli_connect( ) function includes an optional parameter to select a database, and executing queries and buffering results have been logically separated. Annoyingly, many parameter orderings have been changed; in particular, the connection handle is almost always the first parameter to most functions.

Removed functions

Many of the functions we listed as those we don' t use in Chapter 6 have been removed in the new library. Also, persistent connections are no longer available.