Subquery cache
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.