|
|
| Applications |
| Mailing Lists |
| Miscellaneous |
| Operating Systems |
| Programming |
The following features are implemented in MySQL 5.0.
BITData Type: Can be used to store numbers in binary notation. See Section 10.1.1, “Overview of Numeric Typesâ€.Cursors: Elementary support for server-side cursors. For information about using cursors within stored routines, see Section 20.2.9, “Cursorsâ€. For information about using cursors from within the C API, see Section 25.2.7.3, “
mysql_stmt_attr_set()â€.Information Schema: The introduction of the
INFORMATION_SCHEMAdatabase 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 23,INFORMATION_SCHEMATables.Instance Manager: Can be used to start and stop the MySQL Server, even from a remote host. See Section 4.6.9, “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 24, Precision Math.
Storage Engines: Storage engines added in MySQL 5.0 include
ARCHIVEandFEDERATED. See Section 13.8, “TheARCHIVEStorage Engineâ€, and Section 13.7, “TheFEDERATEDStorage Engineâ€.Stored Routines: Support for named stored procedures and stored functions was implemented in MySQL 5.0. See Chapter 20, 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.1.7, “SQL Modesâ€.
Triggers: MySQL 5.0 added limited support for triggers. See Chapter 21, Triggers, and Section 1.8.5.3, “Stored Routines and Triggersâ€.
VARCHARData Type: The effective maximum length of aVARCHARcolumn was increased to 65,535 bytes, and stripping of trailing whitespace was eliminated. (The actual maximum length of aVARCHARis 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 10.4, “String Typesâ€.Views: MySQL 5.0 added support for named, updatable views. See Chapter 22, Views, and Section 1.8.5.5, “Viewsâ€.
XA Transactions: See Section 12.4.7, “XA Transactionsâ€.
MySQL Enterprise. For assistance in maximizing your usage of the many new features of MySQL, subscribe to MySQL Enterprise. For more information see http://www.mysql.com/products/enterprise/advisors.html.
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!Nis the number of tables to be joined. The greedy optimizer reduces this toN!/(D-1)!Dis 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
ANDandORrelations over different keys. (Previously, these were optimized only where both relations in theWHEREclause involved the same key.) This also applies to other one-to-one comparison operators (>,<, and so on), including=and theINoperator. This means that MySQL can use multiple indexes in retrieving results for conditions such asWHERE key1 > 4 OR key2 < 7and even combinations of conditions such asWHERE (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
WHEREclause such ast1.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
ANDandORoperators. See Section 7.2.10, “Nested Join Optimizationâ€, and Section 7.2.11, “Outer Join Simplificationâ€.Optimization of
NOT INandNOT BETWEENrelations, 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 toINandBETWEEN.The
VARCHARdata 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
BITcolumn 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
InnoDBStorage Engine:New compact storage format which can save up to 20% of the disk space required in previous MySQL/
InnoDBversions.Faster recovery from a failed or aborted
ALTER TABLE.Faster implementation of
TRUNCATE.
Performance Improvements in the
NDBCLUSTERStorage Engine:Faster handling of queries that use
INandBETWEEN.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 12.3.2, “
EXPLAINSyntaxâ€, for more information.
(See Chapter 18, MySQL Cluster.)
For those wishing to take a look at the bleeding edge of MySQL development, we make our Bazaar repository for MySQL publicly available. See Section 2.4.15.3, “Installing from the Development Source Treeâ€.
|
Contact Us Legal Notices Order Services Online Pantek Home Privacy Policy IT news Site Map Pantek Library |
User Comments
Add your own comment.