MySQL Internals Manual  /  How MySQL Performs Different Selects  /  Derived Table Execution

12.6 Derived Table Execution

Derived tables is the internal name for subqueries in the FROM clause.

The processing of derived tables is now included in the table opening process (open_and_lock_tables() call). Routine of execution derived tables and substituting temporary table instead of it (mysql_handle_derived()) will be called just after opening and locking all real tables used in query (including tables used in derived table query).

If lex->derived_tables flag is present, all SELECT_LEX structures will be scanned (there is a list of all SELECT_LEX structures in reverse order named lex->all_selects_list, the first SELECT in the query will be last in this list).

There is a pointer for the derived table, SELECT_LEX_UNIT stored in the TABLE_LIST structure (TABLE_LIST::derived). For any table that has this pointer, mysql_derived() will be called.


  • Creates union_result for writing results in this table (with empty table entry, same as for UNIONs).

  • call unit->prepare() to get list of types of result fields (it work correctly for single SELECT, and do not create temporary table for UNION processing in this case).

  • Creates a temporary table for storing results.

  • Assign this temporary table to union_result object.

  • Calls mysql_select or mysql_union to execute the query.

  • If it is not explain, then cleanup JOIN structures after execution (EXPLAIN needs data of optimization phase and cleanup them after whole query processing).

  • Stores pointer to this temporary table in TABLE_LIST structure, then this table will be used by outer query.

  • Links this temporary table in thd->derived_tables for removing after query execution. This table will be closed in close_thread_tables if its second parameter (bool skip_derived) is true.