Oracle 5.0, Reference Manual

Page: 30 / 3232

What Is New in MySQL 5.0
10
• 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 eliminated.
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.1.6, “String Types” .
•
BIT
Data Type. A true
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 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
[880] and
OR
[881]
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 8.3.1.4, “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
[880] and
OR
[881] operators.
See Section 8.3.1.9, “Nested Join Optimization” , and Section 8.3.1.10, “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
.
• XA Transactions. MySQL 5.0 supports XA (distributed) transactions. See Section 13.3.7, “XA
Transactions” .