Select count is very slow

Translated
1

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

Original topic: select count非常慢

| username: TiDBer_8aM3dZG6

[TiDB Usage Environment] Production Environment
[TiDB Version] V6.5.1
[Reproduction Path]
[Encountered Problem: Problem Phenomenon and Impact]
A table with less than 100 million records takes a very long time to execute the following select count query, often taking twenty to thirty seconds. The query condition columns have already been indexed, but the reason for the slowness is unknown.
explain analyze SELECT count(c_ckey)
FROM vm_company_derived_variables_all
WHERE (operated_years > 2)
AND (c_regist_capi > 1000.0)
[Resource Configuration]
One physical machine, virtualized into 10 instances, PD: 3 instances, TIDB: 2 instances, each with 32GB memory, TIKV: 4 instances, each with 32GB memory. SSD hard drive.
[Attachment: Screenshot/Log/Monitoring]
explain analyze as attached
explain.txt (1.8 KB)

2
| username: zhanggame1 | Original post link

Didn’t this question just get posted?
select count() is very slow - :ringer_planet: TiDB Technical Issues / Performance Tuning - TiDB Q&A Community (asktug.com)

It’s naturally slow without using an index, the data volume is too large.

3
| username: Anna | Original post link

Columnar storage TiFlash is relatively fast for statistical queries, I suggest giving it a try.

4
| username: TiDBer_8aM3dZG6 | Original post link

Indexes have been added, but it’s still slow. While installing TiFlash, I found that the CPU does not support AVX2.

5
| username: redgame | Original post link

The log indicates: 1. Data skew. 2. Index failure. 3. Inaccurate statistics.

6
| username: zhanggame1 | Original post link

What is the approximate proportion of the retrieved data to the total data volume?