1 2 next »

New features in MySQL 5.1

Michael Kofler

With new functions MySQL 5.1 hopes more than ever to be THE large database for business.

Since the big leap forward to MySQL 5.0, it's taken the MySQL development team three years, during which there have been a whole string of pre-release versions, to release the new version 5.1 of the popular database. MySQL 5.1.30 (General Availability) is available to download from various mirrors.

The most significant new features in MySQL 5.1 are the partitioning functions. These allow large tables to be distributed across multiple physical files and hard drives. MySQL supports what is known as horizontal partitioning, in which records are selected on the basis of a set criterion and saved in different locations. DATE columns are particularly suitable as criteria for this type of partitioning. Vertical partitioning, i.e. separate storage of different columns, is not supported, but can be implemented relatively easily simply by using a second table – with, for example, the rarely-used BLOG fields.

In the past, database partitioning was frequently used to get around hard drive size limits. In the age of RAID, LVM and terabyte hard drives, the reasons for partitioning have changed – now, it's used because it can significantly speed up database queries. If, for example, a table containing records from 1980 to 2010 is divided into six sub-tables by date, a query such as "SELECT ... WHERE date BETWEEN 2003 AND 2004" need consult only a single sub-table. Rather than processing millions of records, less than 200,000 records need be considered. Theoretically, it is also possible to divide queries using special functions (e.g. SUM and COUNT) across partitioned tables across multiple threads. MySQL does not yet, however, support this form of optimisation. There are also various other restrictions which apply to partitioned tables, concerning issues such as the formulation of partitioning functions or the use of foreign key rules.

Events and record-based replication

Also new is the event scheduler. This component deals with performing predefined SQL commands at regular intervals. The MySQL manual compares these functions with cron-jobs under Unix.

Many applications of this function are possible – regular logging of database status, automatic backup or synchronisation services (although MySQL's replication functions are perhaps more suitable for this), regular generation of tables with frequently used query results (e.g. top 10 lists which are generated hourly or daily and are then instantly available) and so on. One serious disadvantage of such events is that they are an internal MySQL development and are not covered by any SQL standards.

The replication function for synchronising a database across multiple MySQL servers has also been extended. Previously the replication slave simply re-executed all the SQL commands executed on the replication master. The new record-based replication system allows changed data to be transferred directly. This is in some cases more efficient and also allows replication where non-deterministic functions such as UUID() are used. The default is now a mixed mode, in which record-based replication is only used where it is required or where it promises to be faster.

Since MySQL 5.0, the virtual information_schema database has allowed queries by various items of meta-information – what databases and tables exist, what characteristics their columns have, what users, triggers, stored procedures and views are defined, etc. The scope of this database has been significantly extended in MySQL 5.1 and it is an important aid for a variety of administrative tasks.

Plugins, XML, PHP

One useful feature of MySQL is that there are different types of table which can be implemented by different database engines, such as MyISAM and InnoDB. A new feature in MySQL 5.1 is a plugin API, which allows engines to be added and removed retrospectively. The API still appears to be something of a work in progress; each plugin must be recompiled for each subversion of MySQL. Table plugins are currently more of an aid to developers than a user-friendly extension. In the long term, however, table plugins could create an interesting market for suppliers offering MySQL plugins optimised for specific tasks.

A more obviously useful idea is being able to write logs to tables, rather than normal files. MySQL 5.1 offers this option for the general query log and the slow query log (but not for the binary log, which is required for replication). However, it turns out that this function can slow down the MySQL server in some applications. This significantly reduces the usefulness of this new feature, especially as no bug-fix is expected prior to version 6.0.

Users who save XML data in MySQL tables will appreciate two new functions. ExtractValue applies an XPath expression to an XML string, returning a new string as the result. UpdateXML replaces a substring within an XML string selected using an XPath expression with another substring. The two XML functions are highly useful in some circumstances, but are still a long way from making MySQL an XML database.

As far as MySQL programming is concerned, there is an interesting new feature for PHP developers. Starting with PHP 5.3, the mysqlnd library is used as the new backend for the PHP extensions mysql, mysqli and probably also for PDO/mysql. The mysqlnd library thus replaces libmysql, designed for C programmers. The mysqlnd library, which has been created by the MySQL development team, is not only more efficient than libmysql, it can also be directly integrated into PHP code.

The mysqlnd development team have promised that the new backend will not change anything with regard to syntax or use of the mysql, mysqli and PDO/mysql PHP interfaces. This is not entirely accurate however – mysqlnd only supports the secure authentication procedure introduced in MySQL 4.1. Due to compatibility issues, some Linux distributions are still not using this procedure even today (old_passwords=1 in my.cnf). This suggests that there may be login problems when switching to PHP 5.3 which can only be resolved by regenerating all MySQL passwords.

1 2 next »