LATERAL Derived optimization

You are viewing an old version of this article. View the current version here.

Starting from MariaDB 10.3, MariaDB has Lateral Derived optimization, also referred to as "Split Grouping Optimization" in some sources.


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:

create view OCT_TOTALS as
  SUM(amount) as TOTAL_AMT
from orders
  order_date BETWEEN '2017-10-01' and '2017-10-31'
group by

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

select *
  customer, OCT_TOTALS
  customer.customer_id=OCT_TOTALS.customer_id and
  customer.customer_name IN ('Customer#1', '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.

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 EXPLAIN output will look like so:

| id   | select_type     | table      | type  | possible_keys | key         | key_len | ref                  | rows | Extra       |
|    1 | PRIMARY         | customer   | range | PRIMARY,name  | name        | 100     | NULL                 |    2 |             |
|    1 | PRIMARY         | <derived2> | ref   | key0          | key0        | 4       | customer.customer_id |   36 |             |
|    2 | LATERAL DERIVED | orders     | ref   | customer_id   | customer_id | 4       | customer.customer_id |  365 | Using where |

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.

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 optimizer_switch flag. It can be disabled like so:

set optimizer_switch='split_materialized=off'



Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.