MariaDB 5.3.5 delivers faster subqueries
The developers at Monty Program have announced MariaDB 5.3.5, the first stable release of the MariaDB 5.3 series of relational databases. The leading requirement in MariaDB 5.3's development was improved performance and they focussed on improving the query optimiser, introduced in July 2011. The developers now feel is ready for production use.
The developers have particularly worked on subqueries, declaring that the 5.3 series optimisations "finally make subqueries usable". One set of optimisations can be found for semi-join subqueries. Semi-join queries are used to run
IN subqueries in MariaDB 5.3 and use a join optimiser to select one of five optimisations or execution strategies.
One core optimisation, the Table Pullout, can replace sub-queries with a join where appropriate. If the sub-query is not a semi-join, MariaDB 5.3 falls back to other methods including extracting the results of the subquery into a temporary table, or the older
IN-TO-EXISTS optimisation, the only one to be carried forward to MariaDB 5.3. There is also a subquery cache to reduce the number of times already optimised subqueries are re-executed.
A Subquery optimisations map shows which optimisations are implemented in which versions of MariaDB and MySQL. The code of MariaDB 5.3 comes primarily from MySQL 5.1 and 5.5, but also includes some from the never finally released MySQL 6.
MariaDB 5.3.5 is available to download as source and as binaries for 32- and 64-bit Windows, Linux, CentOS 5, Red Hat Enterprise Linux 5 and 32-bit Solaris 11 and 10. Release notes and a full changelog are available for the GPLv2 licensed database.