Oracle has released the General Availability (GA) version of MySQL 5.6, along with version 5.2.46 of the MySQL Workbench GUI tool. The main changes to MySQL 5.6 are the addition of NoSQL access to the InnoDB database engine and faster sub-queries.
MySQL 5.6 sub-queries are now optimized using semi-JOINs. According to a post by Rob Young on the Oracle MySQL blog, the optimizer is now more efficient in handling subqueries in the FROM clause; materialization of subqueries in the FROM clause is now postponed until their contents are needed during execution, greatly improving performance. Additionally, the optimizer may add an index to derived tables during execution to speed up row retrieval. The addition of Index Condition Pushdown (ICP) and Batch Key Access (BKA) is said to improve selected query throughput by up to 280 times.
Online Data Definition Language (DDL) operations are now supported, so you can change the backend schema while keeping the application online. This is achieved by the addition of new ALTER TABLE DLL syntax.
The new version now supports full-text search with InnoDB, and you can build FULLTEXT indexes on InnoDB tables so users can search for words and phrases. The InnoDB full-text search supports Natural language/Boolean modes, proximity search and relevance ranking.
A major addition that recognises the rise of NoSQL is the ability to access InnoDB using NoSQL. MySQL 5.6 uses the industry standard Memcached API, implemented via a new Memcached daemon plug-in to mysqld. The protocol is mapped directly to the native InnoDB API and means you can use existing Memcached clients to go directly to InnoDB data for lookups and updates that are transactionally compliant.
The InnoDB storage engine has been improved to minimize legacy threading, flushing, and contentions and bottlenecks. The changes should enable better concurrency on heavily loaded OLTP systems, and according to Oracle, will result in up to 230 per cent improvement in transactional and read only throughput.
Another improvement is self-healing replication clusters thanks to the addition of global transaction identifiers and utilities to automatically detect and recover from failures. The new version also handles crashes better in replicated systems, as the binary log and slaves automatically recover their correct positions in the replication stream in case of a crash, and resume replication without administrator intervention. Clusters support up to five times faster replication through multi-threaded slaves.
Version 5.2.46 of the MySQL Workbench GUI tool has also been released, with 30 resolved bugs and support for the new MySQL 5.6 server. MySQL Utilities 1.2.0 has also been included on the distribution.
Oracle is planning Virtual Developer Days to introduce MySQL and the new features of version 5.6. The first, for the US and Canada, is on March 12th.