An Overview of the MySQL Engine and its Latest Features
Google TechTalks April 26, 2006 Timour Katchaounov ABSTRACT 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. 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. The talk concludes with the near-future plans for new features coming in the next versions of MySQL.