title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/query-cache.md)

The query cache allows to compute `SELECT` queries just once and to serve further executions of the same query directly from the cache.
Depending on the type of the queries, this can dramatically reduce latency and resource consumption of the ClickHouse server.

## Background, design and limitations[​](#background-design-and-limitations "Direct link to Background, design and limitations")

Query caches can generally be viewed as transactionally consistent or inconsistent.

- In transactionally consistent caches, the database invalidates (discards) cached query results if the result of the `SELECT` query changes
  or potentially changes. In ClickHouse, operations which change the data include inserts/updates/deletes in/of/from tables or collapsing
  merges. Transactionally consistent caching is especially suitable for OLTP databases, for example
  [MySQL](https://dev.mysql.com/doc/refman/5.6/en/query-cache.html) (which removed query cache after v8.0) and
  [Oracle](https://docs.oracle.com/database/121/TGDBA/tune_result_cache.htm).
- In transactionally inconsistent caches, slight inaccuracies in query results are accepted under the assumption that all cache entries are
  assigned a validity period after which they expire (e.g. 1 minute) and that the underlying data changes only little during this period.
  This approach is overall more suitable for OLAP databases. As an example where transactionally inconsistent caching is sufficient,
  consider an hourly sales report in a reporting tool which is simultaneously accessed by multiple users. Sales data changes typically
  slowly enough that the database only needs to compute the report once (represented by the first `SELECT` query). Further queries can be
  served directly from the query cache. In this example, a reasonable validity period could be 30 min.

Transactionally inconsistent caching is traditionally provided by client tools or proxy packages (e.g.
[chproxy](https://www.chproxy.org/configuration/caching/)) interacting with the database. As a result, the same caching logic and
configuration is often duplicated. With ClickHouse's query cache, the caching logic moves to the server side. This reduces maintenance
effort and avoids redundancy.

## Configuration settings and usage[​](#configuration-settings-and-usage "Direct link to Configuration settings and usage")

Note

In ClickHouse Cloud, you must use [query level settings](/docs/operations/settings/query-level) to edit query cache settings. Editing [config level settings](/docs/operations/configuration-files) is currently not supported.

Note

[clickhouse-local](/docs/operations/utilities/clickhouse-local) runs a single query at a time. Since query result caching does not make sense, the query
result cache is disabled in clickhouse-local.

Setting [use\_query\_cache](/docs/operations/settings/settings#use_query_cache) can be used to control whether a specific query or all queries of the
current session should utilize the query cache. For example, the first execution of query

```
SELECT some_expensive_calculation(column_1, column_2)
FROM table
SETTINGS use_query_cache = true;
```

will store the query result in the query cache. Subsequent executions of the same query (also with parameter `use_query_cache = true`) will
read the computed result from the cache and return it immediately.

Note

Setting `use_query_cache` and all other query-cache-related settings only take an effect on stand-alone `SELECT` statements. In particular,
the results of `SELECT`s to views created by `CREATE VIEW AS SELECT [...] SETTINGS use_query_cache = true` are not cached unless the `SELECT`
statement runs with `SETTINGS use_query_cache = true`.

The way the cache is utilized can be configured in more detail using settings [enable\_writes\_to\_query\_cache](/docs/operations/settings/settings#enable_writes_to_query_cache)
and [enable\_reads\_from\_query\_cache](/docs/operations/settings/settings#enable_reads_from_query_cache) (both `true` by default). The former setting
controls whether query results are stored in the cache, whereas the latter setting determines if the database should try to retrieve query
results from the cache. For example, the following query will use the cache only passively, i.e. attempt to read from it but not store its
result in it:

```
SELECT some_expensive_calculation(column_1, column_2)
FROM table
SETTINGS use_query_cache = true, enable_writes_to_query_cache = false;
```

For maximum control, it is generally recommended to provide settings `use_query_cache`, `enable_writes_to_query_cache` and
`enable_reads_from_query_cache` only with specific queries. It is also possible to enable caching at user or profile level (e.g. via `SET use_query_cache = true`) but one should keep in mind that all `SELECT` queries may return cached results then.

The query cache can be cleared using statement `SYSTEM CLEAR QUERY CACHE`. The content of the query cache is displayed in system table
[system.query\_cache](/docs/operations/system-tables/query_cache). The number of query cache hits and misses since database start are shown as events
"QueryCacheHits" and "QueryCacheMisses" in system table [system.events](/docs/operations/system-tables/events). Both counters are only updated for
`SELECT` queries which run with setting `use_query_cache = true`, other queries do not affect "QueryCacheMisses". Field `query_cache_usage`
in system table [system.query\_log](/docs/operations/system-tables/query_log) shows for each executed query whether the query result was written into or
read from the query cache. Metrics `QueryCacheEntries` and `QueryCacheBytes` in system table
[system.metrics](/docs/operations/system-tables/metrics) show how many entries / bytes the query cache currently contains.

The query cache exists once per ClickHouse server process. However, cache results are by default not shared between users. This can be
changed (see below) but doing so is not recommended for security reasons.

Query results are referenced in the query cache by the [Abstract Syntax Tree (AST)](https://en.wikipedia.org/wiki/Abstract_syntax_tree) of
their query. This means that caching is agnostic to upper/lowercase, for example `SELECT 1` and `select 1` are treated as the same query. To
make the matching more natural, all query-level settings related to the query cache and [output formatting](/docs/operations/settings/formats))
are removed from the AST.

If the query was aborted due to an exception or user cancellation, no entry is written into the query cache.

The size of the query cache in bytes, the maximum number of cache entries and the maximum size of individual cache entries (in bytes and in
records) can be configured using different [server configuration options](/docs/operations/server-configuration-parameters/settings#query_cache).

```
<query_cache>
    <max_size_in_bytes>1073741824</max_size_in_bytes>
    <max_entries>1024</max_entries>
    <max_entry_size_in_bytes>1048576</max_entry_size_in_bytes>
    <max_entry_size_in_rows>30000000</max_entry_size_in_rows>
</query_cache>
```

It is also possible to limit the cache usage of individual users using [settings profiles](/docs/operations/settings/settings-profiles) and [settings
constraints](/docs/operations/settings/constraints-on-settings). More specifically, you can restrict the maximum amount of memory (in bytes) a user may
allocate in the query cache and the maximum number of stored query results. For that, first provide configurations
[query\_cache\_max\_size\_in\_bytes](/docs/operations/settings/settings#query_cache_max_size_in_bytes) and
[query\_cache\_max\_entries](/docs/operations/settings/settings#query_cache_max_entries) in a user profile in `users.xml`, then make both settings
readonly: