Book Review: How to Implement Design Patterns in PHP
Nice n' Easy JQuery Image Rotator
Using PHP pspell Spell Check Functions with a Custom Dictionary
Scrollable Tables with Floating Header using CSS
ENUMs, User Preferences, and the MySQL SET Datatype
Getting Set up with Ogre 3D on Ubuntu

MySQL Changes from Versions 4.1 and 5.0 compared with 5.5

Friday, 10 February 12, 4:30 pm
Under the hood, there are multiple performance improvements in MySQL 5.5. These mostly target the InnoDB engine, but there are several that will improve MyISAM tables. However, InnoDB becomes the default storage engine from version 5.5 onwards. InnoDB supports true ACID Transactions (groups of statements which are only committed if they all succeed), Referential Integrity (which enforces foreign key constraints on a DB level), and improved crash recovery. The cost is increased disk space usage, and somewhat higher CPU demands. 5.5's enhancements greatly mitigate this higher CPU overhead however.

Other enhancements in 5.5 include new goodies for the stored procedure lanugage, semi-synchronous replication in addition to the built-in asynchronous replication, events, and pluggable authentication. There are also enhancements to make better use of multi-core server architectures.

Semi-synchronous replication offers greater data integrity in the event of a master database-server crash. With asynchronous replication, where the master writes updates to the replication log and slaves read from the log quite independently, a failover to a slave when the master crashes could quite easily result in dataloss if the slave chosen is not fully up-to-date with the changes at the time of the crash.

In semi-synchronous replication, slaves report their semi-synchronous support to the master when they connect, and if there are any semi-synchronous slaves, the master will remain blocked after any updates until at least one such slave reports that it has replicated the update (or there's a timeout, in which case the master reverts to asynchronous mode until a slave catches up).

Pluggable authentication means that MySQL can use different authentication types when clients connect. Previously, the client's credentials would be simply checked against the Password column of the account row from the mysql.user table that matches the user name provided by the client for the client host. Since MySQL 5.5.7, each account row may specify an authentication plugin that is then invoked to authenticate the client. Plugins could be written to provide pretty much any authentication method, such as PAM, Windows login IDs, LDAP, or Kerberos.

Supplementary Unicode Support: from version 5.5, MySQL adds support for supplementary unicode characters by introducing the following new character sets: utf16, utf32, and utf8mb4. utf16 and utf32 correspond to the UTF-16 and UTF-32 encodings of the Unicode character set, and they both support supplementary characters. Supplementary characters are used in some languages, such as Japanese and Chinese, to define additional characters that are less frequently used and are not defined in the Unicode Basic Multilingual Plane. The new utf8mb4 character set is similar to utf8, but its encoding allows up to four bytes per character to support supplementary characters, compared with the three bytes allowed by utf8.

Table Partitioning enhanced with RANGE COLUMNS and LIST COLUMNS extensions. Partitions are a way to divide large tables into subsets according to whether specific columns fall into a predefined range or list of values. For a table with a great many rows, this can be much more efficient when running queries that depend directly on the column used for partitioning the table as MySQL will be able to scan just a limited number of ranges rather than the whole table. Key caches are now supported for partitioned MyISAM tables via the CACHE INDEX and LOAD INDEX INTO CACHE statements.

SIGNAL and RESIGNAL support. These keywords are the Standard SQL way to 'throw' errors in routines and stored procedures. These errors may be caught by handlers defined using DECLARE ... CONDITION and DECLARE ... HANDLER statements.

XML improvements, including a new LOAD XML INFILE statement which allows you to import data into your database from XML files such as those produced by mysqldump --xml.

IPv6 Support means that MySQL Server can accept TCP/IP connections from clients connecting over IPv6. To permit IPv6 connections in addition to or instead of IPv4 connections, start the server with an appropriate --bind-address option. IPv6 addresses can be specified in account names in statements such as CREATE USER, GRANT, and REVOKE:
CREATE USER 'fred'@'::1' IDENTIFIED BY 'password';

Please enter your comment in the box below. Comments will be moderated before going live. Thanks for your feedback!

Cancel Post

/xkcd/ Supergroup