The following features are implemented in MySQL 5.0:
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 19, INFORMATION_SCHEMA Tables.
Instance Manager. Can be used to start and stop the MySQL Server, even from a remote host. See Section 4.6.10, “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 Section 12.17, “Precision Math”.
Storage Engines. New storage engines were added and performance of others was improved.
Performance Improvements in the
Performance Improvements in the
NDBCLUSTER Storage Engine:
Faster handling of queries that use
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 nonmatching records over the network. This can make such queries 10 to 100 times faster than in MySQL 4.1 Cluster.
See Section 13.8.2, “EXPLAIN Syntax”, for more information.
(See Chapter 17, MySQL Cluster.)
Stored Routines. MySQL 5.0 added support for stored procedures and stored functions. See Section 18.2, “Using Stored Routines (Procedures and Functions)”.
Triggers. MySQL 5.0 added limited support for triggers. See Section 18.3, “Using Triggers”.
Views. MySQL 5.0 added support for named, updatable views. See Section 18.4, “Using Views”.
Cursors. Elementary support for server-side cursors. For information about using cursors within stored routines, see Section 13.6.6, “Cursors”. For information about using cursors from within the C API, see Section 220.127.116.11, “mysql_stmt_attr_set()”.
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.1.7, “Server SQL Modes”.
VARCHAR Data Type.
The effective maximum length of a
VARCHAR column was increased
to 65,535 bytes, and stripping of trailing whitespace was
VARCHAR 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 actual maximum length of a
VARCHAR is determined by the
maximum row size and the character set you use. The maximum
effective column length is subject to a
row size of 65,535 bytes, which is shared among all
columns.) See Section 11.4, “String Types”.
BIT Data Type.
BIT column type is
available that can be used to store numbers in binary
notation. 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. See
Section 11.1.1, “Numeric Type Overview”.
Optimizer enhancements. Several optimizer improvements were made 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
N is the number of tables to be
joined. The greedy optimizer reduces this to
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
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
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
(key1 > 4 OR key2 < 7) AND (key3 >= 10 OR key4 =
Section 18.104.22.168, “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
NOT IN and
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
XA Transactions. MySQL 5.0 supports XA (distributed) transactions. See Section 13.3.7, “XA Transactions”.