There have been impressive claims about MySQL 5.5, and you’ll find reports detailing speed improvements of up to 4,000%. Take those with a large pinch of salt, but you can generally expect version 5.5 to be faster.
Let’s take a closer look at the best of the new features…
InnoDB Becomes The Default Storage Engine
It’s about time. There are many, many, many reasons why InnoDB is a better choice for most applications, yet MyISAM had up to now remained the default. From MySQL 5.5, developers will have out-of-the-box reliability and stability with ACID-compliant transactions, foreign key support and crash recovery.
In addition, the latest InnoDB storage engine offers better performance, scalability and recovery from catastrophic failure.
Performance has been improved in multi-server replicated systems with:
- Semi-synchronous replication
To improve the reliability of failover, the master can wait until at least one slave has logged the transaction.
- Replication heartbeat
The master sends a message to all slave nodes at frequent intervals. Slaves therefore know when the master has failed, and it helps estimate the delay between master and slave updates.
New LOAD XML Command
LOAD XML reads data from an XML file into a table. Three different formats are supported:
<row column1="value1" column2="value2" .../>
<row> <column1>value1</column1> <column2>value2</column2> </row>
<row> <field name="column1">value1</field> <field name="column2">value2</field> </row>
New SIGNAL/RESIGNAL Commands
Both commands allow you to implement exception handling within stored procedures, functions, events, triggers.
SIGNAL is effectively the same as the ‘throw’ command in PHP and other languages. It allows you to pass back an error number, SQLSTATE value and message to the calling code.
RESIGNAL lets you propagate a SIGNAL exception, perhaps after further doing further work such as data clean-ups. RESIGNAL can pass the original or a modified version of the error.
New TO_SECONDS() Function
Handling dates and times is not always as easy as you’d expect. The file system, PHP interpreter and MySQL itself have different methods of storing times, so many developers resort to the UNIX timestamp or other integer formats.
TO_SECONDS() could help resolve some issues; given any date or datetime value, it returns the number of seconds since year 0 (or NULL if you pass any other value).
Until now, a client connecting to the MySQL server had to pass a user name and password which was authenticated against a record in the mysql.user table. It’s now possible to create a plugin which handles authentication using your own systems and credentials.
Improved Performance on Windows
Many businesses install MySQL on Windows servers. This often makes sense; they already have IT administrators with Windows expertise, but need a cost-effective database solution or want to install a web application which requires MySQL.
The MySQL development team has tidied the system and improved performance on Win32 and Win64 platforms.
Have you evaluated MySQL 5.5? Have you experienced any performance gains? Will you be upgrading? Has the 5.5 release reassured you about Oracle’s commitment to the database?
Craig is a freelance UK web consultant who built his first page for IE2.0 in 1995. Since that time he's been advocating standards, accessibility, and best-practice HTML5 techniques. He's created enterprise specifications, websites and online applications for companies and organisations including the UK Parliament, the European Parliament, the Department of Energy & Climate Change, Microsoft, and more. He's written more than 1,000 articles for SitePoint and you can find him @craigbuckler.