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

Preface

Recently I optimized several slow‑query interfaces in my company and would like to share the insights.

We use MySQL 8 with InnoDB. Apart from index tuning, most of the performance problem comes from count(*) queries.

1. Why count(*) can be slow

In MySQL, count(*) simply counts rows, 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, so it must read each row and aggregate, which becomes very slow for large tables.

2. How to improve count(*) performance

2.1 Add Redis cache

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

2.2 Use a second‑level cache

For queries that rarely change but are frequently read, use an in‑memory cache such as Caffeine or Guava. In SpringBoot 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);
}

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

2.3 Execute queries in parallel

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

2.4 Reduce unnecessary joins

If the required columns already exist in the main table, remove joins to other tables; counting a single table is much faster.

2.5 Switch to ClickHouse for massive analytics

For extremely large datasets, replicate the necessary data to ClickHouse, a column‑store database, and query counts there. Use Canal to sync MySQL binlog to ClickHouse.

When using ClickHouse, batch inserts are recommended to avoid excessive write overhead.

3. Comparison of count() variants

count(*) and count(1) have similar performance and are the fastest.

count(id) is slower because it must read the primary‑key column.

count(indexed_column) is slower still, as it must check for NULL values.

count(non_indexed_column) is the slowest, requiring a full table scan.

count(*) ≈ count(1) > count(id) > count(indexed_column) > count(non_indexed_column)

In short, count(*) is the most efficient way to obtain row totals, but be careful not to confuse it with select *.