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

SELECT ... WHERE 5 = a 

to

SELECT ...WHERE a = 5 

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

This diagram shows the structure of the function handle_select() in /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 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.