MySQL derived table (Derived table) Merge optimization

This article will show you what the derived table (Derived tables) is in MySQL. and MySQL optimized for it.

Background

Like the next table:

Mysql> descCity ;+------------+-------------+------+-----+---------+-------+|Field|Type| Null | Key | Default |Extra|+------------+-------------+------+-----+---------+-------+|Country| varchar( +)|YES|     | NULL    |       ||Population| int( One)|YES|     | NULL    |       ||City| varchar( +)|YES|     | NULL    |       |+------------+-------------+------+-----+---------+-------+

For example, if you first consider a city with a population of more than 10,000 people and then select those cities in Germany, you can write this sql:

SELECT *  from (SELECT*fromWHERE>*1000 as big_cityWHERE  big_city.country='   Germany';

Use the EXPLAIN command to view the execution plan:

Mysql>EXPLAINSELECT *  from(SELECT *  fromCityWHEREPopulation> 1* +) asBig_cityWHEREBig_city.country='Germany' ;+----+-------------+------------+------+---------------+------+---------+------+------+-------------+|Id|Select_type| Table      |Type|Possible_keys| Key  |Key_len|Ref|Rows|Extra|+----+-------------+------------+------+---------------+------+---------+------+------+-------------+|  1 | PRIMARY     | <Derived2> |  All  | NULL          | NULL | NULL    | NULL | 4068 |Usingwhere ||  2 |DERIVED|City|  All  |Population| NULL | NULL    | NULL | 4079 |Usingwhere |+----+-------------+------------+------+---------------+------+---------+------+------+-------------+2Rowsinch Set(0.60Sec

Note: MySQL 5.7 needs to be set Derived_merge=off to have the results above. Otherwise MySQL will merge the temporary table into the outer query, see my other article, "Two temporary tables in MySQL".

The approach of MySQL is:

Experience the following 3 steps:

1. Execute subquery: (SELECT * from the city WHERE population > 1*1000), as in the query statement;
2. The result of the query is written to the temporary table big_city;
3. Read back, apply the where condition of the upper select Big_city.country= ' Germany '.

The execution of such subqueries is very inefficient because the base table city is not scanned with a high-selectivity condition of the parent selection (country = ' Germany '). We read too many records from the city table, and then we have to write them to a temporary table and read them again before we can filter them out.

Derived table Merge in action

If you run this query in Mariadb/mysql 5.6, you can get the following results:

MariaDB[ World]>EXPLAINSELECT *  from(SELECT *  fromCityWHEREPopulation> 1* +) asBig_cityWHEREBig_city. Country='Germany';+----+-------------+-------+------+--------------------+---------+---------+-------+------+------------------- -----------------+|Id|Select_type| Table |Type|Possible_keys| Key     |Key_len|Ref|Rows|Extra|+----+-------------+-------+------+--------------------+---------+---------+-------+------+------------------- -----------------+|  1 |Simple|City|Ref|Population,country|Country| 3       |Const|    - |UsingIndexCondition Usingwhere |+----+-------------+-------+------+--------------------+---------+---------+-------+------+------------------- -----------------+1Rowinch Set(0.00Sec

From the above results can be seen:

1. Only one line of output, indicating that the subquery has been merged into the Superior's SELECT statement;
2. The city table is accessed through the Country column, and country= ' Germany ' is used to construct the ref access on the table;
3. The query reads about 90 rows, which is a big improvement for the previous 4079-line read plus 4068 lines of temporary table read/write.

Factsheet

Derived tables (subqueries in the FROM clause) can be in no grouping, aggregates, or ORDER by ... The LIMIT clauses are merged into their parent queries. This optimization is turned on by default and can be turned off as follows:

Set @ @optimizer_switch = ' Derived_merge=off '

The Maria and MySQL versions that do not support this optimization will perform subqueries, which can lead to a well-known bug (see e.g. MySQL bug #44802), starting with the mariadb 5.3+ and MySQL 5.6+, explain commands are executed immediately, regardless Derived_merge how to set up.

Reference:

Derived Table Merge Optimization

MySQL derived table (Derived table) Merge optimization