Code and Concepts MySQL

This section discusses key optimizer concepts, terminology, and how these are reflected in the MySQL server source code.


This description uses a narrow definition: The optimizer is the set of routines which decide what execution path the DBMS should take for queries. MySQL changes these routines frequently, so you should compare what is said here with what's in the current source code. To make that easy, this description includes notes referring to the relevant file and routine, such as “See: /sql/select_cc, optimize_cond()”. A transformation occurs when one query is changed into another query which delivers the same result.For example, a query could be changed from

Most transformations are less obvious. Some transformations result in faster execution.

The Optimizer Code

This diagram shows the structure of the function handle_select() in /sql/ server code that handles a query):

The indentation in the diagram shows what calls what.Thus you can see that handle_select()calls mysql_select() which calls JOIN::prepare() which calls setup_fields(), and so on. The first part of mysql_select() is JOIN::prepare() which is for context analysis, metadata setup, and some subquery transformations. The optimizer is JOIN::optimize() and all its subordinate routines. When the optimizer finishes, JOIN::exec() takes over and does the job that JOIN::optimize() decides upon. Although the word “JOIN” appears, these optimizer routines are applicable to all query types.The optimize_cond() and opt_sum_query() routines perform transformations. The make_join_statistics() routine puts together all the information it can find about indexes that might be useful for accessing the query's tables.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

MySQL Topics