Why count(*) Slows Down MySQL and How to Optimize It

Introduction

Recently I optimized several slow‑query interfaces in my company and would like to share the lessons learned, focusing on MySQL 8 with InnoDB and the performance issues of count(*) in pagination queries.

Why does count(*) perform poorly?

In MySQL the cost of count(*) depends on the storage engine. MyISAM stores the total row count on disk, so count(*) can return the value instantly. InnoDB uses MVCC and must scan rows to accumulate the count, which becomes slow for large tables.

How to improve count(*) performance

1. Add Redis cache

For simple counters such as total page views, store the count in Redis and update it on each request instead of querying the database.

This eliminates the need for a real‑time count(*) query, but it may cause temporary inconsistency under high concurrency.

2. Use a second‑level cache

For queries with many filter conditions, cache the result set in an in‑memory cache such as Caffeine or Guava. In Spring Boot you can annotate the method with @Cacheable and provide a custom key generator.

@Cacheable(value = "brand", keyGenerator = "cacheKeyGenerator")
public BrandModel getBrand(Condition condition) {
    return getBrandByCondition(condition);
}

Cached data can be reused for a few minutes, greatly reducing the cost of count(*).

3. Execute counts in parallel

When you need separate counts (e.g., valid vs. invalid orders), run the two SELECT count(*) … WHERE status = … statements in parallel using CompletableFuture and combine the results.

4. Reduce unnecessary joins

If the required columns are already in the main table, avoid joining auxiliary tables in the count(*) query. A simplified query that scans only the target table can improve performance dramatically.

5. Switch to a column‑store database

For heavy analytical workloads, replicate the data to ClickHouse, a column‑store that can return billions of rows in seconds. Use Canal to capture MySQL binlog changes and keep ClickHouse synchronized.

Performance comparison of count variants

count(*)

and count(1) are fastest. count(id) is slower. count(indexed_column) is slower still. count(non_indexed_column) requires a full table scan and is the slowest.

Therefore, count(*) remains the most efficient choice for total row counts.