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.
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
<derived2>
is accessed through key0
. ref
column shows world.Country.Code
ref
access: Country.Code=cities_in_country.Country
. set optimizer_switch='derived_with_keys=off'
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/derived-table-with-key-optimization/