Optimizations for derived tables

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

The idea

Users of "big" database systems are used to using FROM subqueries as a way to structure their queries. For example, if one's first thought was that they needed a list reasonably-big cities, and then that the cities are needed for Germany, one would end up with this SQL:

select * 
from 
  (select * from City where Population > 10*1000) as big_city 
where 
  big_city.Country='DEU'

MariaDB [world]> explain select * from (select * from City where Population > 1*1000) as big_city where big_city.Country='DEU' ;

Automatic

See also

Optimizing Subqueries in the FROM Clause in MySQL 5.6 manual