(mysql.info) mysql-5-0-nutshell
Info Catalog
(mysql.info) roadmap
(mysql.info) roadmap
1.6.1 What's New in MySQL 5.0
-----------------------------
The following features are implemented in MySQL 5.0.
* *`BIT' Data Type*: Can be used to store numbers in binary
notation. See numeric-type-overview.
* *Cursors*: Elementary support for server-side cursors. For
information about using cursors within stored routines, see
cursors. For information about using cursors from within the C
API, see 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 information-schema.
* *Instance Manager*: Can be used to start and stop the MySQL
Server, even from a remote host. See 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 precision-math.
* *Storage Engines*: Storage engines added in MySQL 5.0 include
`ARCHIVE' and `FEDERATED'. See archive-storage-engine, and
federated-storage-engine.
* *Stored Routines*: Support for named stored procedures and stored
functions was implemented in MySQL 5.0. See
stored-procedures.
* *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 server-sql-mode.
* *Triggers*: MySQL 5.0 added limited support for triggers. See
triggers, and ansi-diff-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 string-types.
* *Views*: MySQL 5.0 added support for named, updatable views. See
views, and ansi-diff-views.
* *XA Transactions*: See xa.
* *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!', where N
is the number of tables to be joined. The greedy optimizer
reduces this to `N!/(D-1)!', where 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 (`>', `<', 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
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
can be reduced to
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 nested-joins, and
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'.
(See innodb.)
* *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 explain, for more information.
(See ndbcluster.)
For those wishing to take a look at the bleeding edge of MySQL
development, we make our BitKeeper repository for MySQL publicly
available. See installing-source-tree.
Info Catalog
(mysql.info) roadmap
(mysql.info) roadmap
automatically generated byinfo2html