MySQL Forge

Resources for the MySQL Community

Category: MySQLDevelopment

MySQL Internals Selects

← Back to MySQL Internals overview page

Contents 1 How MySQL Performs Different Selects 1.1 Steps of Select Execution 1.2 Class 1.3 SIMPLE or PRIMARY SELECT 1.4 Structure Of Complex Select 1.5 Non-Subquery UNION Execution 1.6 Derived Table Execution 1.7 Subqueries 1.8 Single Select Engine 1.9 Union Engine 1.10 Special Engines 1.11 Explain Execution

[edit] How MySQL Performs Different Selects

[edit] Steps of Select Execution

Every select is performed in these base steps:

• JOIN::prepare
  • Initialization and linking JOIN structure to st_select_lex.
  • fix_fields() for all items (after fix_fields(), we know everything about item).
  • Moving HAVING to WHERE if possible.
  • Initialization procedure if there is one.
• JOIN::optimize
  • Single select optimization.
  • Creation of first temporary table if needed.
• JOIN::exec
  • Performing select (a second temporary table may be created).
• JOIN::cleanup
  • Removing all temporary tables, other cleanup.
• JOIN::reinit
  • Prepare all structures for execution of SELECT (with JOIN::exec).

[edit] Class

This class has a very important role in SELECT performance with select_result class and classes inherited from it (usually called with a select_ prefix). This class provides the interface for transmitting results.

The key methods in this class are the following:

• send_fields sends given item list headers (type, name, etc.).
• send_data sends given item list values as row of table of result.
• send_error is used mainly for error interception, making some operation and then ::send_error will be called.

For example, there are the following select_result classes:

• select_send used for sending results though network layer.
• select_export used for exporting data to file.
• multi_delete used for multi-delete.
• select_insert used for INSERT ... SELECT ...
• multi_update used for multi-update.
• select_singlerow_subselect used for row and scalar subqueries..
• select_exists_subselect used for EXISTS/IN/ALL/ANY/SOME subqueries.
• select_max_min_finder_subselect used for min/max subqueries (ALL/ANY subquery optimization).

[edit] SIMPLE or PRIMARY SELECT

For performing single primary select, SELECT uses the mysql_select function, which does:

• allocate JOIN
• JOIN::prepare
• JOIN::optimize
• JOIN::exec
• JOIN::cleanup

In previous versions of MySQL, all SELECT operations were performed with the help of this function and mysql_select() was not divided into parts.

[edit] Structure Of Complex Select

There are two structures that describe selects:

• st_select_lex (SELECT_LEX) for representing SELECT itself
• st_select_lex_unit (SELECT_LEX_UNIT) for grouping several selects in a bunch

The latter item represents UNION operation (the absence of UNION is a union with only one SELECT and this structure is present in any case). In the future, this structure will be used for EXCEPT and INTERSECT as well.

For example:

(SELECT ...) UNION (SELECT ... (SELECT...)...(SELECT...UNION...SELECT))
   1           2      3           4             5        6       7

will be represented as:

------------------------------------------------------------------------
                                                                  level 1
 SELECT_LEX_UNIT(2)
 |
 +---------------+
 |               |
 SELECT_LEX(1)   SELECT_LEX(3)
                 |
 --------------- | ------------------------------------------------------
                 |                                                level 2
                 +-------------------+
                 |                   |
                 SELECT_LEX_UNIT(4)  SELECT_LEX_UNIT(6)
                 |                   |
                 |                   +--------------+
                 |                   |              |
                 SELECT_LEX(4)       SELECT_LEX(5)  SELECT_LEX(7)
 
 ------------------------------------------------------------------------
 

Note: Single subquery 4 has its own SELECT_LEX_UNIT.

The uppermost SELECT_LEX_UNIT (#2 in example) is stored in LEX. The first and uppermost SELECT_LEX (#1 in example) is stored in LEX, too. These two structures always exist.

At the time of creating or performing any JOIN::* operation, LEX::current_select points to an appropriate SELECT_LEX.

Only during parsing of global ORDER BY and LIMIT clauses (for the whole UNION), LEX::current_select points to SELECT_LEX_UNIT of this unit, in order to store this parameter in this SELECT_LEX_UNIT. SELECT_LEX and SELECT_LEX_UNIT are inherited from st_select_lex_node.

[edit] Non-Subquery UNION Execution

Non-subquery unions are performed with the help of mysql_union(). For now, it is divided into the following steps:

• st_select_lex_unit::prepare (the same procedure can be called for single SELECT for derived table => we have support for it in this procedure, but we will not describe it here):
  • Create select_union (inherited from select_result) which will write select results in this temporary table, with empty temporary table entry. We will need this object to store in every JOIN structure link on it, but we have not (yet) temporary table structure.
  • Allocate JOIN structures and execute JOIN::prepare() for every SELECT to get full information about types of elements of SELECT list (results). Merging types of result fields and storing them in special Items (Item_type_holder) will be done in this loop, too. Result of this operation (list of types of result fields) will be stored in st_select_lex_unit::types).
  • Create a temporary table for storing union results (if UNION without ALL option, 'distinct' parameter will be passed to the table creation procedure).
  • Assign a temporary table to the select_union object created in the first step.
• st_select_lex_unit::exec
  • Delete rows from the temporary table if this is not the first call.
  • if this is the first call, call JOIN::optimize else JOIN::reinit and then JOIN::exec for all SELECTs (select_union will write a result for the temporary table). If union is cacheable and this is not the first call, the method will do nothing.
  • Call mysql_select on temporary table with global ORDER BY and LIMIT parameters after collecting results from all SELECTs. A special fake_select_lex (SELECT_LEX) which is created for every UNION will be passed for this procedure (this SELECT_LEX also can be used to store global ORDER BY and LIMIT parameters if brackets used in a query).

[edit] 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.

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.

[edit] Subqueries

In expressions, subqueries (that is, subselects) are represented by Item inherited from Item_subselect.

To hide difference in performing single SELECTs and UNIONs, Item_subselect uses two different engines, which provide uniform interface for access to underlying SELECT or UNION (subselect_single_select_engine and subselect_union_engine, both are inherited from subselect_engine).

The engine will be created at the time Item_subselect is constructed (Item_subselect::init method).

On Item_subselect::fix_fields(), engine->prepare() will be called.

Before calling any value-getting method (val, val_int, val_str, bring_value (in case of row result)) engine->exec() will be called, which executes the query or just does nothing if subquery is cacheable and has already been executed.

Inherited items have their own select_result classes. There are two types of them:

• select_singlerow_subselect, to store values of given rows in Item_singlerow_subselect cache on send_data() call, and report error if Item_subselect has 'assigned' attribute.
• select_exists_subselect just store 1 as value of Item_exists_subselect on send_data() call. Since Item_in_subselect and Item_allany_subselect are inherited from Item_exists_subselect, they use the same select_result class.

Item_subselect will never call the cleanup() procedure for JOIN. Every JOIN::cleanup will call cleanup() for inner JOINs. The uppermost JOIN::cleanup will be called by mysql_select() or mysql_union().

[edit] Single Select Engine

subselect_single_select_engine:

• constructor allocate JOIN and store pointers on SELECT_LEX and JOIN.
• prepare() call JOIN::prepare.
• fix_length_and_dec() prepare cache and receive type and parameters of returning items (called only by Item_singlerow_subselect).
• exec() drop 'assigned' flag of Item_subselect. If this is the first time, call JOIN::optimize and JOIN::exec(), else do nothing or JOIN::reinit() JOIN::exec() depending on type of subquery.

[edit] Union Engine

subselect_union_engine:

• constructor just store pointer to st_select_lex_union (SELECT_LEX_UNION).
• prepare() call st_select_lex_unit::prepare.
• fix_length_and_dec() prepare cache and receive type and parameters (maximum of length) of returning items (called only by Item_singlerow_subselect).
• exec() call st_select_lex_unit::exec(). st_select_lex_unit::exec() can drop 'assigned' flag of Item_subselect if st_select_lex_unit::item is not 0.

[edit] Special Engines

There are special engines used for optimization purposes. These engines do not have a full range of features. They can only fetch data. The normal engine can be replaced with such special engines only during the optimization process.

Now we have two such engines:

• subselect_uniquesubquery_engine used for:

left_expression IN (SELECT primary_key FROM table WHERE conditions)

This looks for the given value once in a primary index, checks the WHERE condition, and returns was it found or not?

• subselect_indexsubquery_engine used for:

left_expression IN (SELECT any_key FROM table WHERE conditions)

This first looks up the value of the left expression in an index (checking the WHERE condition), then if value was not found, it checks for NULL values so that it can return NULL correctly (only if a NULL result makes sense, for example if an IN subquery is the top item of the WHERE clause then NULL will not be sought)

The decision about replacement of the engine happens in JOIN::optimize, after calling make_join_readinfo, when we know what the best index choice is.

[edit] Explain Execution

For an EXPLAIN statement, for every SELECT, mysql_select will be called with option SELECT_DESCRIBE.

For main UNION, mysql_explain_union will be called.

For every SELECT in a given union, mysql_explain_union will call mysql_explain_select.

mysql_explain_select will call mysql_select with option SELECT_DESCRIBE.

mysql_select creates a JOIN for select if it does not already exist (it might already exist because if it called for subquery JOIN can be created in JOIN::optimize of outer query when it decided to calculate the value of the subquery). Then it calls JOIN::prepare, JOIN::optimize, JOIN::exec and JOIN::cleanup as usual.

JOIN::exec is called for SELECT with SELECT_DESCRIBE option call select_describe.

select_describe returns the user description of SELECT and calls mysql_explain_union for every inner UNION.

PROBLEM: how it will work with global query optimization?