If a query uses a derived table (or a view), the first action that the query optimizer will attempt is to apply the derived-table-merge-optimization and merge the derived table into its parent select. However, that optimization is only applicable when the select inside the derived table has a join as the top-level operation. If it has a GROUP-BY, DISTINCT, or uses window functions, then derived-table-merge-optimization is not applicable.

In that case, the Condition Pushdown optimization is applicable.

Introduction to Condition Pushdown

Consider an example

The naive way to execute the above is to

1. Compute the OCT_TOTALS contents (for all customers).

2. The, select the line with customer_id=1

This is obviously inefficient, if there are 1000 customers, then one will be doing up to 1000 times more work than necessary.

However, the optimizer can take the condition customer_id=1 and push it down into the OCT_TOTALS view.

Inside the OCT_\TOTALS, the added condition is put into its HAVING clause, so we end up with:

Then, parts of HAVING clause that refer to GROUP BY columns are moved into the WHERE clause:

Once a restriction like customer_id=1 is in the WHERE, the query optimizer can use it to construct efficient table access paths.

Controlling the Optimization

The optimization is enabled by default. One can disable it by setting the flag condition_pushdown_for_derived to OFF.

The pushdown from HAVING to WHERE part is controlled by condition_pushdown_from_having flag in .

See Also

• Condition Pushdown through Window Functions (since )

• (since )

• The Jira task for the feature is .

_{This page is licensed: CC BY-SA / Gnu FDL}

Background

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 to select cities with population greater than 10,000 people, and then that from these cities to select those that are located in Germany, one could write this SQL:

For MySQL, using such syntax was taboo. If you run EXPLAIN for this query, you can see why:

It plans to do the following actions:

From left to right:

1. Execute the subquery: (SELECT * FROM City WHERE Population > 1*1000), exactly as it was written in the query.

2. Put result of the subquery into a temporary table.

3. Read back, and apply a WHERE condition from the upper select, big_city.Country='DEU'

Executing a subquery like this is very inefficient, because the highly-selective condition from the parent select, (Country='DEU') is not used when scanning the base table City. We read too many records from theCity table, and then we have to write them into a temporary table and read them back again, before finally filtering them out.

Derived table merge in action

If one runs this query in MariaDB/MySQL 5.6, they get this:

From the above, one can see that:

1. The output has only one line. This means that the subquery has been merged into the top-level SELECT.

2. Table City is accessed through an index on the Country column. Apparently, the Country='DEU' condition was used to construct ref access on the table.

Factsheet

• Derived tables (subqueries in the FROM clause) can be merged into their parent select when they have no grouping, aggregates, or ORDER BY ... LIMIT clauses. These requirements are the same as requirements for VIEWs to allow algorithm=merge.

• The optimization is enabled by default. It can be disabled with:

• Versions of MySQL and MariaDB which do not have support for this optimization will execute subqueries even when running EXPLAIN. This can result in a well-known problem (see e.g. ) of EXPLAIN statements taking a very long time. Starting from + and MySQL 5.6+ EXPLAIN commands execute instantly, regardless of the derived_merge setting.

See Also

• FAQ entry:

_{This page is licensed: CC BY-SA / Gnu FDL}

This is another name for Lateral Derived Optimization.

_{This page is licensed: CC BY-SA / Gnu FDL}

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

• 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:

See Also

• in MySQL 5.6 manual

_{This page is licensed: CC BY-SA / Gnu FDL}

MariaDB supports the Lateral Derived optimization, also referred to as "Split Grouping Optimization" or "Split Materialized Optimization" in some sources.

Description

The optimization's use case is

• The query uses a derived table (or a VIEW, or a non-recursive CTE)

• The derived table/View/CTE has a GROUP BY operation as its top-level operation

• The query only needs data from a few GROUP BY groups

An example of this: consider a VIEW that computes totals for each customer in October:

And a query that does a join with the customer table to get October totals for "Customer#1" and Customer#2:

Before Lateral Derived optimization, MariaDB would execute the query as follows:

1. Materialize the view OCT_TOTALS. This essentially computes OCT_TOTALS for all customers.

2. Join it with table customer.

The EXPLAIN would look like so:

It is obvious that Step #1 is very inefficient: we compute totals for all customers in the database, while we will only need them for two customers. (If there are 1000 customers, we are doing 500x more work than needed here)

Lateral Derived optimization addresses this case. It turns the computation of OCT_TOTALS into what SQL Standard refers to as "LATERAL subquery": a subquery that may have dependencies on the outside tables. This allows pushing the equality customer.customer_id=OCT_TOTALS.customer_id down into the derived table/view, where it can be used to limit the computation to compute totals only for the customer of interest.

The query plan will look as follows:

1. Scan table customer and find customer_id for Customer#1 and Customer#2.

2. For each customer_id, compute the October totals, for this specific customer.

The EXPLAIN output will look like so:

Note the line with id=2: select_type is LATERAL DERIVED. And table customer uses ref access referring to customer.customer_id, which is normally not allowed for derived tables.

In EXPLAIN FORMAT=JSON output, the optimization is shown like so:

Note the "lateral": 1 member.

Controlling the Optimization

Lateral Derived is enabled by default. The optimizer will make a cost-based decision whether the optimization should be used.

If you need to disable the optimization, it has an flag. It can be disabled like so:

References

• Jira task:

• Commit:

_{This page is licensed: CC BY-SA / Gnu FDL}