Derived Table Execution MySQL

“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. mysql_derived():

  • 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.

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

MySQL Topics