Subquery cache

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

Goal

The goal of the subquery cache is optimize evaluation of correlated subqueries by storing results togather with correlation parameters in the cache and avoiding re-execution of the subquery in case if result is already in the cache.

Administration

By default the cache is on. One can switch it on or off using optimizer_switch subquery_cache:

 set optimizer_switch='subquery_cache=off';

Eficiancy of the query cache is visible in 2 statistics variables:

  • Subquery_cache_hit - Global counter for all subquery caches hits.
  • Subquery_cache_miss - Global counter for all subquery caches miss.

Visibility

Using of the cache is visible in EXTENDED EXPLAIN output (warnings) as <expr_cache><list of parameters>(cached expression). For example:

MariaDB [test]> explain extended select * from t1 where a in (select b from t2);
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

MariaDB [test]> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                    |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` where (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`b`)))) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

In the example above <expr_cache><`test`.`t1`.`a`>(...) is sign of using the subquery cache.

Implementation

Every subqyery cache creates temporary table where result and all parameters stored. It has unique index over all parameters. Originally the cache created in heap table (if it is immpossible the cache becomes disabled for this expression). When the table grows it could be turned into MyISAM or Aria table according to the rules used for all temporarty tables.

Comments

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.