Is there result caching in TiDB queries?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TIDB 查询的时候有结果缓存吗?

| username: lemonade010

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Reproduction Path] Does TiDB have result caching for queries? For select * query it takes 0.01S, for select count() it takes 2S. Using explain analyze to query the execution plan, the time consumption is the same, but directly selecting count() takes much longer than select *. The query result has only 3 rows.
[Encountered Problem: Problem Phenomenon and Impact]
[Resource Configuration] Enter TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

| username: xfworld | Original post link

There are cases of cache hits. If the query is the same and it was queried last time, it will be directly retrieved from the cache…

You can try more and observe.

| username: lemonade010 | Original post link

How can I determine if the cache was used?

| username: 小龙虾爱大龙虾 | Original post link

There is caching, but in your case, it may not necessarily be affected by the cache. Try it a few more times first.

| username: 我是人间不清醒 | Original post link

The execution info column in the execution result contains copr_cache_hit_ratio information, indicating the hit rate of the coprocessor cache. The 0.75 in the above example means the hit rate is approximately 75%.

Viewing the Grafana Monitoring Dashboard

In the Grafana monitoring, under the tidb namespace and the distsql subsystem, you can see the copr-cache panel. This panel monitors the hit count, miss count, and cache discard count of the coprocessor cache across the entire cluster.

| username: zhanggame1 | Original post link

Using EXPLAIN ANALYZE + SQL to view the execution plan, it includes the cache hit rate.

| username: lemonade010 | Original post link

The explain analyze +sql copr_cache_hit_ratio shows all zeros, and the execution time is over 2 seconds, but when I actually execute the select, it takes only 0.01 seconds.

| username: 濱崎悟空 | Original post link

There is result caching.

| username: 有猫万事足 | Original post link

Here it is. The time difference you mentioned later proves that the cache does indeed exist. Otherwise, how could there be a 200-fold difference?

| username: YuchongXU | Original post link

Yes, there is.

| username: TIDB-Learner | Original post link

There is a cache.

| username: lemonade010 | Original post link

I want to understand under what circumstances caching is used and under what circumstances indexes are not used.

| username: 友利奈绪 | Original post link

If the result of SELECT * exists in TiDB’s cache, the execution speed may be faster because it directly retrieves the result from the cache. However, SELECT COUNT(*) may not be affected by this caching effect because it needs to perform aggregation operations on the actual data.

| username: TiDB_C罗 | Original post link

My cache hit rate is so low

| username: 有猫万事足 | Original post link

Both of your questions are quite complex.

First, there is a cache for execution plans.

Hitting this cache can skip the step of generating an execution plan.
Then, TiDB has a cache.

The coprocessor-cache mentioned above is of this type.
Finally, TiKV also has a cache,
RocksDB has a block-cache.

Whether to use an index involves cost evaluation. Each situation is different. You can roughly refer to the following post.

| username: lemonade010 | Original post link

Do some research.

| username: zhaokede | Original post link

Are the conditions consistent?
TiDB supports result caching through TiKV cache, TiDB Server cache, and pushdown computation result cache to improve query efficiency.

| username: lemonade010 | Original post link

Conditions are consistent.

| username: TiDBer_ZxWlj6A1 | Original post link

Whether a count query benefits from caching depends on the specific query scenario and the characteristics of the table. If the table is a cached table and the query can utilize the data from the cached table, then there may be a caching effect. However, if the query requires extensive computation or the table data is not in the cached table, then the count query will not benefit from caching.

| username: TiDBer_H5NdJb5Q | Original post link

SQL parsing has a cache, but the execution results of SQL, as I understand it, are not cached and cannot be cached, right? The count can change at any time, so if there were a cache, when would it expire?