This section provides a snapshot of the MySQL development roadmap, including major features implemented in or planned for various MySQL releases. The following sections provide information for each release series.
The current production release series is MySQL 5.0, which was declared stable for production use as of MySQL 5.0.15, released in October 2005. The previous production release series was MySQL 4.1, which was declared stable for production use as of MySQL 4.1.7, released in October 2004. “Production status” means that future 5.0 and 4.1 development is limited only to bugfixes. For the older MySQL 4.0 and 3.23 series, only critical bugfixes are made.
Active MySQL development is currently taking place in the MySQL 5.0 and 5.1 release series, and new features are being added only to the latter.
Before upgrading from one release series to the next, please see the notes in Section 2.11, “Upgrading MySQL”.
The most requested features and the versions in which they were implemented or are scheduled for implementation are summarized in the following table:
| Feature | MySQL Series |
| Foreign keys | 3.23 (for the InnoDB storage engine) |
| Unions | 4.0 |
| Subqueries | 4.1 |
| R-trees | 4.1 (for the MyISAM storage engine) |
| Stored procedures | 5.0 |
| Views | 5.0 |
| Cursors | 5.0 |
| XA transactions | 5.0 |
| Foreign keys | 5.2 (implemented in 3.23 for InnoDB) |
| Triggers | 5.0 and 5.1 |
| Partitioning | 5.1 |
| Pluggable Storage Engine API | 5.1 |
| Row-Based Replication | 5.1 |
The following features are implemented in MySQL 5.0.
BIT Data
Type: Can be used to store numbers in binary
notation. See Section 11.1.1, “Overview of Numeric Types”.
Cursors: Elementary support
for server-side cursors. For information about using cursors
within stored routines, see Section 17.2.9, “Cursors”. For
information about using cursors from within the C API, see
Section 22.2.7.3, “mysql_stmt_attr_set()”.
Information Schema: The
introduction of the INFORMATION_SCHEMA
database in MySQL 5.0 provided a standards-compliant means
for accessing the MySQL Server's metadata; that is, data
about the databases (schemas) on the server and the objects
which they contain. See
Chapter 20, The INFORMATION_SCHEMA Database.
Instance Manager: Can be used to start and stop the MySQL Server, even from a remote host. See Section 5.5, “mysqlmanager — The MySQL Instance Manager”.
Precision Math: MySQL 5.0 introduced stricter criteria for acceptance or rejection of data, and implemented a new library for fixed-point arithmetic. These contributed to a much higher degree of accuracy for mathematical operations and greater control over invalid values. See Chapter 21, Precision Math.
Storage Engines: Storage
engines added in MySQL 5.0 include
ARCHIVE and FEDERATED.
See Section 14.8, “The ARCHIVE Storage Engine”, and
Section 14.7, “The FEDERATED Storage Engine”.
Stored Routines: Support for named stored procedures and stored functions was implemented in MySQL 5.0. See Chapter 17, Stored Procedures and Functions.
Strict Mode and Standard Error Handling: MySQL 5.0 added a strict mode where by it follows standard SQL in a number of ways in which it did not previously. Support for standard SQLSTATE error messages was also implemented. See Section 5.2.5, “The Server SQL Mode”.
Triggers: MySQL 5.0 added limited support for triggers. See Chapter 18, Triggers, and Section 1.9.5.4, “Stored Routines and Triggers”.
VARCHAR Data
Type: The maximum effective length of a
VARCHAR column was increased to 65,532
bytes, and stripping of trailing whitespace was eliminated.
See Section 11.4, “String Types”.
Views: MySQL 5.0 added support for named, updatable views. See Chapter 19, Views, and Section 1.9.5.6, “Views”.
XA Transactions: See Section 13.4.7, “XA Transactions”.
Performance enhancements: A number of improvements were made in MySQL 5.0 to improve the speed of certain types of queries and in the handling of certain types. These include:
MySQL 5.0 introduces a new “greedy”
optimizer which can greatly reduce the time required to
arrive at a query execution plan. This is particularly
noticeable where several tables are to be joined and no
good join keys can otherwise be found. Without the
greedy optimizer, the complexity of the search for an
execution plan is calculated as
N!N is the number of tables to
be joined. The greedy optimizer reduces this to
N!/(D-1)!D is the depth of the
search. Although the greedy optimizer does not guarantee
the best possible of all execution plans (this is
currently being worked on), it can reduce the time spent
arriving at an execution plan for a join involving a
great many tables — 30, 40, or more — by a
factor of as much as 1,000. This should eliminate most
if not all situations where users thought that the
optimizer had hung when trying to perform joins across
many tables.
Use of the Index Merge method to
obtain better optimization of AND and
OR relations over different keys.
(Previously, these were optimized only where both
relations in the WHERE clause
involved the same key.) This also applies to other
one-to-one comparison operators
(>, <, and
so on), including = and the
IN operator. This means that MySQL
can use multiple indexes in retrieving results for
conditions such as WHERE key1 > 4 OR key2
< 7 and even combinations of conditions
such as WHERE (key1 > 4 OR key2 < 7) AND
(key3 >= 10 OR key4 = 1). See
Section 7.2.6, “Index Merge Optimization”.
A new equality detector finds and optimizes
“hidden” equalities in joins. For example,
a WHERE clause such as
t1.c1=t2.c2 AND t2.c2=t3.c3 AND t1.c1 < 5
implies these other conditions
t1.c1=t3.c3 AND t2.c2 < 5 AND t3.c3 < 5
These optimizations can be applied with any combination
of AND and OR
operators. See Section 7.2.10, “Nested Join Optimization”, and
Section 7.2.11, “Outer Join Simplification”.
Optimization of NOT IN and
NOT BETWEEN relations, reducing or
eliminating table scans for queries making use of them
by mean of range analysis. The performance of MySQL with
regard to these relations now matches its performance
with regard to IN and
BETWEEN.
The VARCHAR data type as implemented
in MySQL 5.0 is more efficient than in previous
versions, due to the elimination of the old (and
nonstandard) removal of trailing spaces during
retrieval.
The addition of a true BIT column
type; this type is much more efficient for storage and
retrieval of Boolean values than the workarounds
required in MySQL in versions previous to 5.0.
Performance Improvements in the
InnoDB Storage Engine:
New compact storage format which can save up to 20%
of the disk space required in previous
MySQL/InnoDB versions.
Faster recovery from a failed or aborted
ALTER TABLE.
Faster implementation of
TRUNCATE.
Performance Improvements in the
NDBCluster Storage Engine:
Faster handling of queries that use
IN and
BETWEEN.
Condition pushdown: In cases involving the comparison of an unindexed column with a constant, this condition is “pushed down” to the cluster where it is evaluated in all partitions simultaneously, eliminating the need to send non-matching records over the network. This can make such queries 10 to 100 times faster than in MySQL 4.1 Cluster.
See Section 7.2.1, “Optimizing Queries with EXPLAIN”, for more information.
(See Chapter 15, MySQL Cluster.)
For those wishing to take a look at the bleeding edge of MySQL development, we make our BitKeeper repository for MySQL publicly available. See Section 2.9.3, “Installing from the Development Source Tree”.