# 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 [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3)/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:

```sql
SELECT * 
FROM
   Country, 
   (SELECT 
       SUM(City.Population) AS urban_population, 
       City.Country 
    FROM City 
    GROUP BY City.Country 
    HAVING 
    urban_population > 1*1000*1000
   ) AS cities_in_country
WHERE 
  Country.Code=cities_in_country.Country AND Country.Continent='Europe';
```

The EXPLAIN output for it will show:

```
+----+-------------+------------+------+-------------------+-----------+---------+--------------------+------+---------------------------------+
| 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 |
+----+-------------+------------+------+-------------------+-----------+---------+--------------------+------+---------------------------------+
```

One can see here that

* table `<derived2>` is accessed through `key0`.
* `ref` column shows `world.Country.Code`
* if we look that up in the original query, we find the equality that was used to construct `ref` access: `Country.Code=cities_in_country.Country`.

## 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
* The optimization is ON by default; it can be switched off like so:

```sql
SET optimizer_switch='derived_with_keys=off'
```

## See Also

* [Optimizing Subqueries in the FROM Clause](https://dev.mysql.com/doc/refman/5.6/en/from-clause-subquery-optimization.html) in MySQL 5.6 manual
* [What is MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3)
* [Subquery Optimizations Map](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/subquery-optimizations-map)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimizations-for-derived-tables/derived-table-with-key-optimization.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.