# Aggregate Operations Solutions (Continued...)

+ Data in tables is infrequently update
  + Enable**copr-cache** on the compute node**TiDB** (TiDB 4.0 introduced, enabled by default)
  + Determined to be an optimization for**copr-cache**

```4
explain analyze select count(*) from t1 where dt>'2021-11-25 20:39:58' ;
+------------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+-----------+------+
| id                           | estRows     | actRows  | task      | access object | execution info                                                                                                                                               | operator info                                | memory    | disk |
+------------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+-----------+------+
| StreamAgg_20                 | 1.00        | 1        | root      |               | time:1.49ms, loops:2                                                                                                                                         | funcs:count(Column#7)->Column#5              | 380 Bytes | N/A  |
| └─TableReader_21             | 1.00        | 10       | root      |               | time:1.49ms, loops:2, cop_task: {num: 10, max: 1.45ms, min: 596.5µs, avg: 802.1µs, p95: 1.45ms, rpc_num: 10, rpc_time: 7.92ms, copr_cache_hit_ratio: 1.00}   | data:StreamAgg_9                             | 779 Bytes | N/A  |
|   └─StreamAgg_9              | 1.00        | 10       | cop[tikv] |               | tikv_task:{proc max:3.26s, min:515ms, p80:3.25s, p95:3.26s, iters:9770, tasks:10}                                                                            | funcs:count(1)->Column#7                     | N/A       | N/A  |
|     └─Selection_19           | 1204500.00  | 1200000  | cop[tikv] |               | tikv_task:{proc max:3.26s, min:515ms, p80:3.25s, p95:3.26s, iters:9770, tasks:10}                                                                            | gt(sbtest.t1.dt, 2021-11-25 20:39:58.000000) | N/A       | N/A  |
|       └─TableFullScan_18     | 10000000.00 | 10000000 | cop[tikv] | table:t1      | tikv_task:{proc max:3.24s, min:508ms, p80:3.23s, p95:3.24s, iters:9770, tasks:10}                                                                            | keep order:false                             | N/A       | N/A  |
+------------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+-----------+------+
```

------------------------------------------------------------

```
explain analyze select count(*) from t1 where dt>'2021-11-25 20:39:58' ;
+------------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+-----------+------+
| id                           | estRows     | actRows  | task      | access object | execution info                                                                                                                                               | operator info                                | memory    | disk |
+------------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+-----------+------+
| StreamAgg_20                 | 1.00        | 1        | root      |               | time:1.49ms, loops:2                                                                                                                                         | funcs:count(Column#7)->Column#5              | 380 Bytes | N/A  |
| └─TableReader_21             | 1.00        | 10       | root      |               | time:1.49ms, loops:2, cop_task: {num: 10, max: 1.45ms, min: 596.5µs, avg: 802.1µs, p95: 1.45ms, rpc_num: 10, rpc_time: 7.92ms, copr_cache_hit_ratio: 1.00}   | data:StreamAgg_9                             | 779 Bytes | N/A  |
|   └─StreamAgg_9              | 1.00        | 10       | cop[tikv] |               | tikv_task:{proc max:3.26s, min:515ms, p80:3.25s, p95:3.26s, iters:9770, tasks:10}                                                                            | funcs:count(1)->Column#7                     | N/A       | N/A  |
|     └─Selection_19           | 1204500.00  | 1200000  | cop[tikv] |               | tikv_task:{proc max:3.26s, min:515ms, p80:3.25s, p95:3.26s, iters:9770, tasks:10}                                                                            | gt(sbtest.t1.dt, 2021-11-25 20:39:58.000000) | N/A       | N/A  |
|       └─TableFullScan_18     | 10000000.00 | 10000000 | cop[tikv] | table:t1      | tikv_task:{proc max:3.24s, min:508ms, p80:3.23s, p95:3.24s, iters:9770, tasks:10}                                                                            | keep order:false                             | N/A       | N/A  |
+------------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+-----------+------+
```

In the above example, the first execution is 0.89 seconds, and the intermediate data does not have DML. When query again, the execution is completed in less than 10ms. Let's look at the explain analyze output of SQL: copr_cache_hit_ratio: 1.00, and we can determine that it is an optimization of copr-cache. The SQL execution time that would have required Tikv to scan large amounts of data has been reduced from 0.89 seconds to less than 10ms.