The Ultimate Guide to High Availability with MariaDB

Subquery cache

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

The goal of the subquery cache is to optimize the evaluation of correlated subqueries by storing results together with correlation parameters in a cache and avoiding re-execution of the subquery in cases where the result is already in the cache.

Note: This page describes features in the source repository for MariaDB 5.3

There are currently no official packages or binaries available for download which contain the features. If you want to try out any of the new features described here you will need to get and compile the code yourself.

Administration

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

set optimizer_switch='subquery_cache=on';

The efficiency of the subquery cache is visible in 2 statistics variables:

  • subquery_cache_hit - Global counter for all subquery cache hits.
  • subquery_cache_miss - Global counter for all subquery cache misses.

Visibility

Your usage 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 the presence of "<expr_cache><`test`.`t1`.`a`>(...)" is how you know you are using the subquery cache.

Implementation

Every subquery cache creates a temporary table where the results and all parameters are stored. It has a unique index over all parameters. First the cache is created in a heap table (if doing this is impossible the cache becomes disabled for that expression). When the table grows it could be turned into MyISAM or Aria table according to the rules used for all temporary tables.

See Also:

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.