1 of 1

Derived Table with Key Optimization

The idea

If a derived table cannot be merged into its parent SELECT, it will be materialized in a temporary table, and then parent select will treat it as a regular base table.

Before /MySQL 5.6, the temporary table would never have any indexes, and the only way to read records from it would be a full table scan. Starting from the mentioned versions of the server, the optimizer has an option to create an index and use it for joins with other tables.

Example

Consider a query: we want to find countries in Europe, that have more than one million people living in cities. This is accomplished with this query:

The EXPLAIN output for it will show:

One can see here that

table <derived2> is accessed through key0.
ref column shows world.Country.Code

Factsheet

The idea of "derived table with key" optimization is to let the materialized derived table have one key which is used for joins with other tables.
The optimization is applied then the derived table could not be merged into its parent SELECT
- which happens when the derived table doesn't meet criteria for mergeable VIEW

Derived Table with Key Optimization

The idea

If a derived table cannot be merged into its parent SELECT, it will be materialized in a temporary table, and then parent select will treat it as a regular base table.

Example

Consider a query: we want to find countries in Europe, that have more than one million people living in cities. This is accomplished with this query:

The EXPLAIN output for it will show:

One can see here that

table <derived2> is accessed through key0.
ref column shows world.Country.Code

Factsheet

The idea of "derived table with key" optimization is to let the materialized derived table have one key which is used for joins with other tables.
The optimization is applied then the derived table could not be merged into its parent SELECT
- which happens when the derived table doesn't meet criteria for mergeable VIEW

+----+-------------+------------+------+-------------------+-----------+---------+--------------------+------+---------------------------------+
| id | select_type | table      | type | possible_keys     | key       | key_len | ref                | rows | Extra                           |
+----+-------------+------------+------+-------------------+-----------+---------+--------------------+------+---------------------------------+
|  1 | PRIMARY     | Country    | ref  | PRIMARY,continent | continent | 17      | const              |   60 | Using index condition           |
|  1 | PRIMARY     | <derived2> | ref  | key0              | key0      | 3       | world.Country.Code |   17 |                                 |
|  2 | DERIVED     | City       | ALL  | NULL              | NULL      | NULL    | NULL               | 4079 | Using temporary; Using filesort |
+----+-------------+------------+------+-------------------+-----------+---------+--------------------+------+---------------------------------+

Derived Table with Key Optimization

The idea

Example

Factsheet

See Also

Derived Table with Key Optimization

The idea

Example

Factsheet

See Also