One of the main benefits of database technology is the declarative SQL interface to access—one tells a DBMS what is needed and the DBMS automatically finds a way to efficiently retrieve the requested data. As with every other modern DBMS, MySQL has a component, called the "query optimizer," that performs this complex discovery task.

The first part of this talk describes the main principles behind MySQL's query optimizer and execution engine, how the optimizer transforms queries into executable query plans, what these plans look like, and how they are executed.

The second part of the talk describes the major improvements in the query engine of MySQL 5.0, and how these improvements can benefit the users of MySQL 5.0. The "greedy" optimizer reduces compilation time for big queries with orders of magnitude. The "index merge" access method provides a way to use more than one index for the same query. Through "equality propagation" the optimizer discovers automatically new conditions that allow it to discover better indexes and better join orders. For faster plan execution and to allow better join orders, the 5.0 optimizer transforms most outer joins into inner joins. The outer joins that cannot be transformed into inner ones are executed in a pipeline manner, so that no intermediate results need to be materialized. Finally, some GROUP BY and DISTINCT queries can be executed much faster thanks to "loose index scan" technique that reads only a fraction of an index.

Talk concludes with the near-future plans for new optimizer features coming in the next versions of MySQL.