Why count(*) Is Slow in MySQL InnoDB and How to Speed It Up

Preface

Recently I optimized several slow‑query interfaces in my company and want to share the lessons learned, focusing on MySQL 8 with the InnoDB storage engine.

1. Why is count(*) slower?

In MySQL, count(*) counts all rows in a table, but its performance depends on the storage engine. MyISAM stores the total row count on disk, so count(*) is fast. InnoDB supports transactions and MVCC, which means the row count must be read and summed row by row, leading to poor performance on large tables.

2. How to improve count(*) performance?

2.1 Add Redis cache

For simple counters (e.g., total page views), cache the count in Redis and increment it on each visit instead of querying the database each time.

When a user opens the page, increment the cached value: count = count + 1. The cached value can be returned directly, avoiding real‑time count(*) queries.

2.2 Use a second‑level cache

For scenarios with many query conditions where indexes are ineffective, store query results in an in‑memory cache (e.g., Caffeine or Guava). In Spring Boot you can annotate the method with @Cacheable:

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

Define a CacheKeyGenerator to build a unique key from the method parameters.

public class CacheKeyGenerator implements KeyGenerator {
    @Override
    public Object generate(Object target, Method method, Object... params) {
        return target.getClass().getSimpleName() + "_" + method.getName() + "," +
               StringUtils.arrayToDelimitedString(params, ",");
    }
}

The cached result expires after a short period (e.g., 5 minutes), dramatically reducing count(*) latency.

2.3 Execute queries in parallel

When you need separate counts (e.g., valid vs. invalid orders), run the two count(*) statements concurrently using CompletableFuture and combine the results.

CompletableFuture<Long> valid = CompletableFuture.supplyAsync(() ->
    jdbcTemplate.queryForObject("select count(*) from order where status=1", Long.class));
CompletableFuture<Long> invalid = CompletableFuture.supplyAsync(() ->
    jdbcTemplate.queryForObject("select count(*) from order where status=0", Long.class));
Long[] result = CompletableFuture.allOf(valid, invalid)
    .thenApply(v -> new Long[]{valid.join(), invalid.join()})
    .join();

2.4 Reduce unnecessary joins

If the required filter columns already exist in the main table, avoid joining extra tables. Replace a multi‑join count query with a single‑table query that uses the foreign‑key columns directly, which improves performance.

select count(*) from product where name='Test' and unit_id=123 and brand_id=124 and category_id=125;

2.5 Switch to ClickHouse for massive analytics

When joins cannot be eliminated, consider replicating the data to ClickHouse, a column‑store database optimized for fast aggregation. Use Canal to capture MySQL binlog changes and keep ClickHouse in sync. Queries against ClickHouse can make count(*) orders of magnitude faster.

Note: Insert data into ClickHouse in batches rather than one row at a time.

If query conditions are extremely diverse, ElasticSearch can be an alternative, though it also suffers from deep‑pagination issues.

3. Performance comparison of count variants

count(*) – scans rows without extra processing.

count(1) – similar to count(*).

count(id) – reads primary‑key column.

count(indexed_column) – reads an indexed column and checks for NULL.

count(non_indexed_column) – full table scan with NULL check.

Performance order (high to low): count(*) ≈ count(1) > count(id) > count(indexed_column) > count(non_indexed_column).

Therefore, count(*) is generally the fastest among the count functions, but it should not be confused with select *.