Improvements to MySQL optimiser

At its Open World conference, Oracle has presented a pre-release version of MySQL 5.6 that is called both "Developer Milestone Release 5.6.3" and "DMR 5.6 #2". The developers say that the optimiser has especially been given an overhaul in this milestone release. In a blog posting, Rob Young writes that the improvements enable MySQL to execute certain queries up to three times faster.

This has, says Young, a particularly noticeable effect on queries which use ORDER BY to sort by a non-indexed column but have a small LIMIT value. Previously, such queries caused the server to scan the entire table, sort it, create temporary tables, eliminate un-needed rows and merge the results of that process to create a result set. The change is designed to ensure that after a full scan of the table, only the needed rows are sorted and returned in the result set. Young added that substantial speed is also gained by the storage engine executing certain index-oriented operations itself.

The developers have also extended the EXPLAIN command to support displaying the execution plan for the INSERT, UPDATE and DELETE commands. An additional TRACE option is now available which allows developers to trace the optimiser's decision-making steps. The MySQL milestone is available in the download area of the developer site.

Update: This article has been corrected to better reflect the optimisation in query processing.

(djwm)