MySQL
5
Zylink offers MySQL 5.0.24 along
with either PHP 4 or PHP 5. What is
new with MySQL 5? Below are just a few of the enhancements in MySQL 5
as
per MySQL documentation:
Be sure to check out
our hosting
plans |
|
WEB DEVELOPMENT
CONFERENCE |
|
Zylink
announces its 2007 Open Source Web Development Conference
focusing on PHP 5 and MySQL 5.0
Learn more. |
|
|
|
|
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!, where
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.
(See
Section 14.2, “The InnoDB Storage
Engine”.)
- Performance Improvements in the
NDBCluster Storage Engine:
Check out our Linux
Plans or Windows Plans
Return to Home Page
|
