Release candidate for MySQL 5.6 available
At the MySQL Connect conference in San Francisco, Oracle has presented the release candidate for the next version of its open source relational database, MySQL. For MySQL 5.6, the developers have focused on adding features to InnoDB, for example, implementing full-text searches that were previously only available in the non-transactional MyISAM, and on improving server performance by improving database's optimiser.
Oracle's presentation clearly indicated that InnoDB is likely to replace many other storage engines in the medium term. The company's Vice President of MySQL Engineering, Thomas Ulin, repeatedly pointed out that the developers are exclusively focusing their work on InnoDB.
Oracle says that the throughput of InnoDB tables has been greatly improved and that it handles, for example, twice as many read transactions per second as in version 5.5, and almost four times as many write transactions. InnoDB tables can now also be saved on any number of physical devices, this functionality is due to a feature called "portable table spaces".
The server now performs almost all DDL (data definition language) statements on InnoDB tables online. Copying the table in the background is, therefore, no longer necessary, and applications can continue to access the table while the change is being made.
Changes to the optimiser result in individual queries being handled faster than before. Sub-queries in connection with IN() such as
SELECT t FROM film WHERE film_id
IN (SELECT film_id FROM film_actor
GROUP BY film_id HAVING count(*) > 12)
were practically unusable in previous versions of MySQL, according to Oracle. The optimiser now uses semi-joins and subquery materialisation. Oracle says that this has allowed MySQL to execute query 18 in the DBT3 database benchmark in a reasonable amount of time – while the estimated execution time used to be about 45 days, the test can now be completed within seconds.
Oracle noted that queries with a sorted result set that is returned in portions through LIMIT have been accelerated because sorted intermediate tables are no longer created. Instead, the server provides a buffer for the results that are stipulated by LIMIT and this buffer is sorted continually.
Index Condition Pushdown (ICP) is the developers' name for a procedure that involves evaluating all parts of a WHERE clause using indexes. Unlike previously, table rows to which the clause doesn't apply to won't even be read. This optimisation mainly saves disk I/O resources.
Additionally, multi-range read and batched key access procedures are now available for InnoDB tables. They make use of the fact that in such tables, the data records on disk are sorted according to the primary key. Oracle says that, consequently, every data block only needs to be read once. Where batched key access is used for joins, however, any potential performance improvements strongly depend on the size of the join buffer.
Another optimisation affects subqueries in the FROM part of a SELECT statement, which are also known as derived tables. In MySQL 5.6, the optimiser ensures that such tables are only generated when they are required. They are particularly redundant when using EXPLAIN and in cases where a sub-query returns an empty result. If the table does need to be created, the server will, with certain queries, also generate an index for it, which accelerates JOIN operations.
When using IN() clauses that involve a large number of values, previous versions of MySQL often required longer to create the query plan than to execute the query, said Oracle. Apparently, this is now set to change because the optimiser no longer attempts to establish the exact number of potential rows, relying on index statistics instead.
A comprehensive description of all new MySQL 5.6 features is available online. Binary versions for Linux, Mac OS X, BSD, Solaris and Windows, as well as the source code of the release candidate can be found in the download area on the MySQL web site.