Optimizations for derived tables
Contents
Derived tables are subqueries in the FROM
clause. Prior to MariaDB 5.3/MySQL 5.6, they were too slow to be usable. In MariaDB 5.3/MySQL 5.6, there are two optimizations
- Derived table merge
- Automatic index creation for derived tables
which provide adequate performance.
Derived table merge
Derived table with key
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/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:
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 throughkey0
. ref
column showsworld.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:
set optimizer_switch='derived_merge=off'
See also
Optimizing Subqueries in the FROM Clause in MySQL 5.6 manual