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
A transformation occurs when one query is changed into another query which delivers the same result. For example, a query could be changed from
SELECT ... WHERE 5 = a
SELECT ...WHERE a = 5
Most transformations are less obvious. Some transformations result in faster execution.
This diagram shows the structure of the function
/sql/sql_select.cc (the server code that
handles a query):
handle_select() mysql_select() JOIN::prepare() setup_fields() JOIN::optimize() /* optimizer is from here ... */ optimize_cond() opt_sum_query() make_join_statistics() get_quick_record_count() choose_plan() /* Find the best way to access tables */ /* as specified by the user. */ optimize_straight_join() best_access_path() /* Find a (sub-)optimal plan among all or subset */ /* of all possible query plans where the user */ /* controls the exhaustiveness of the search. */ greedy_search() best_extension_by_limited_search() best_access_path() /* Perform an exhaustive search for an optimal plan */ find_best() make_join_select() /* ... to here */ JOIN::exec()
The indentation in the diagram shows what calls what. Thus you
can see that
mysql_select() which calls
JOIN::prepare() which calls
setup_fields(), and so on. The first part of
JOIN::prepare() which is for context
analysis, metadata setup, and some subquery transformations. The
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.
opt_sum_query() routines perform
routine puts together all the information it can find about
indexes that might be useful for accessing the query's tables.