There are many advantages in upgrading to MySQL 5.5. It brings about significant performance gains for all areas of operation, thanks to refactoring geared towards making better use of multi-core server architecture.
If you use InnoDB – the database engine in MySQL that 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 good crash recovery – you will see much greater performance gains than if you use MyISAM.
However, because it is so lightweight, MyISAM remains the faster engine, but the gap between the two has been shortened considerably with this update.
Another benefit of upgrading is that extra functionality is available in 5.5. MySQL is a very mature standards-compliant product, and so unsurprisingly these new features are not earth-shattering. There is a new LOAD XML INFILE statement which allows you to import data from XML files, for instance the XML files that mysqldump can generate when run with the –xml option.
MySQL 5.5 also brings support for supplementary character sets. These are Unicode characters that are not frequently used, and include certain Japanese and Chinese characters. To support these rare characters, MySQL now offers three new character sets: utf16, utf32, and utf8mb4.
There is also an extension to table partitioning, which is a feature for splitting large data tables into partitions, according to the values of specific columns. Since MySQL 5.5, the partition ranges can be specified as either a range of column values, or a list.
The last improvement worth mentioning that is of interest to MySQL users (i.e. programmers), is the addition of the standard SQL error handling keywords, SIGNAL and RESIGNAL. These keywords provide a way to throw and re-throw errors in stored procedures in much the same way that other programming languages throw and re-throw exceptions. MySQL’s existing DECLARE … CONDITION and DECLARE … HANDLER statements are used for catching and handling these types of errors.
There are a variety of other new features that are really only of interest to database admins – such as IPv6 support, improved and extensible authentication methods, and enhancements to MySQL replication. You can find a little more information on these on my blog at http://www.rdeeson.com/weblog/116/mysql-changes-from-versions-41-and-50-compared-with-55.html