跪拜 Guibai
← Back to the summary

Stop Tuning COUNT(*): Ask What the Number Is For, Then Move It Out of the Table

Interviewer asks "How to optimize COUNT(*) on a large MySQL table?" — 90% of people get the first step wrong

Last week a reader complained to me: an 80 million-row order table, product wanted to show "total number of orders" on the admin homepage. He wrote SELECT COUNT(*) FROM t_order, it ran for 8 seconds, the interface timed out, and his leader called him in for a talk.

This question appears frequently in big-tech interviews, but most people immediately answer "count(1) is faster than count(*)" or "add a cache", and that basically sinks the interview. Because it looks like a SQL question, but it actually tests requirements clarification + architecture selection + consistency fallback. This article explains the underlying principles, six concrete solutions, complete code, and a pitfall checklist, suitable for backend developers who have been burned by this question or are preparing for an interview.

1. Don't rush to write SQL; the first reaction of a strong candidate is to ask back

In real development, you often get a one-line requirement: "the page needs to show the total." If you just start coding, you're likely wasting effort. The first step that separates good developers is asking these questions:

What to ask Why it matters Direct impact on solution
Do you need exact value or magnitude? Can 0.1% error be tolerated? Exact → counter table / Redis; magnitude → estimated value
Real-time requirement? Seconds / minutes / T+1 Determines if async or offline batching is possible Seconds → Redis; T+1 → offline snapshot table
Read/write QPS? Does it have WHERE conditions? Determines hot spots and concurrency paths Full-table statistics and conditional statistics are two different approaches
Data volume (million / ten-million / billion)? Single table or sharded? Determines "index scan" vs "must build external count" Billion-level single table → external counting system
Can you introduce external components (Redis / MQ / Canal)? Determines implementation cost Cannot → only counter table / summary table

Why ask this? Because a large-table COUNT(*) is essentially not "how to make SQL fast" but the "exact + real-time + large table" impossible triangle — you can have at most two of the three. Accept approximation, and you can return in milliseconds. If you need exact and high-frequency, you must strip the count out of the database.

⚠️ Special note: a count with a WHERE condition is a different problem. Counter tables and Redis only solve "full-table or fixed-dimension" statistics. Once conditions are dynamically combined, you must go back to "covering index + pre-aggregation". For complex scenarios, hand it directly to an OLAP engine.

2. Principle layer: Why InnoDB's COUNT(*) can't be fast

Understand "why it's slow" before discussing optimization, otherwise everything is castles in the air.

2.1 MyISAM returns instantly, InnoDB struggles — what's the difference

Dimension MyISAM InnoDB
COUNT(*) implementation Stores a row counter in header metadata, returns directly Does not store a total; must scan an index tree and count on the fly
Performance O(1) constant time O(N), grows linearly with data size
Transaction safety Not supported Supported
Row-level locks Not supported (table-level locks) Supported
Crash recovery Requires repair Automatic recovery

MyISAM writes are serial, so it can safely store the total row count in metadata. InnoDB cannot; the root cause is two words: MVCC.

image.png

At the same moment, different transactions see different "total row counts", so storing a fixed number is meaningless to everyone. So each count must: pick an index tree → traverse leaf nodes → for each row, check via the ReadView "is this row visible to me?" → accumulate. Scanning several million or several ten-million rows is bound to be slow.

2.2 Why does adding a secondary index speed up count?

There's a "little trick" in the optimizer: InnoDB executing COUNT(*) will automatically pick the smallest secondary index to scan. A secondary index leaf node stores only "index column + primary key", which is much smaller than the clustered index (which stores the full row). The same 16KB page can hold more entries — fewer pages scanned = less I/O.

So for a large table with no secondary index, deliberately creating a narrow index (like a tinyint status column) can make count 5~10 times faster. Note that this doesn't mean the index remembers the row count; it just narrows the scan range. I've been burned by this before: if the field allows NULL, the optimizer may reject the index, so "an index built for counting" must be a small NOT NULL field.

Verification is one command:

EXPLAIN SELECT COUNT(*) FROM t_order;
-- Look at:
-- type: index       → full index scan (better than ALL full table scan)
-- key:  idx_status  → which index is actually used (not PRIMARY is normal)
-- Extra: Using index → covering index, optimal path

2.3 count(*), count(1), count(id), count(field) — don't be misled by old rumors

Form Semantics InnoDB execution Performance
COUNT(*) Counts all rows Traverses smallest index tree, doesn't fetch values, just counts 🥇 Fastest, officially recommended
COUNT(1) Equivalent to COUNT(*) Puts constant 1 per row and counts, same execution path 🥇 No difference from COUNT(*)
COUNT(主键id) Primary key non-null, result equals total rows Must fetch primary key value, extra fetch overhead 🥈 Slightly slower
COUNT(字段) Counts only rows where field is non-NULL Fetches value + checks NULL, may do table lookups if index doesn't cover 🥉 Slowest

✅ Conclusion: prioritize COUNT(*) in any scenario. "count(1) is faster than count(*)" is a leftover rumor from the Oracle era. Under MySQL 5.7/8.0 InnoDB, the two execution plans are completely identical; the official documentation has long confirmed this. Arguing about it is a waste of time.

What's worse is the semantic trap: count(age) skips rows where age is NULL, so the result may differ from count(*). Use the right one first, then talk about speed.

There's also a hidden failure point — don't use count for concurrent duplicate checking:

-- ❌ Never do this: under RR, count is a consistent snapshot read without locks
BEGIN;
SELECT COUNT(*) FROM t_user WHERE phone = '138xxxx';  -- both concurrent transactions see 0
INSERT INTO t_user(phone) VALUES ('138xxxx');          -- inserts two duplicate rows
COMMIT;

For duplicate checking, use unique index + INSERT ... ON DUPLICATE KEY UPDATE / INSERT IGNORE instead. Rely on the database's unique constraint and row locks, not on count.

3. Solution selection: a decision chart handles it

With the principles clear, choosing a solution is easy. Follow the three steps by "can you accept error → data volume → consistency requirements":

image.png

Memorize the mantra: fast and exact → counter table; high throughput → Redis + reconciliation; magnitude only → estimate; trend only → offline summary; small table → just count.

Below we break down solutions from L0 to L6 by implementation difficulty, each with copy-paste-ready code. If you're preparing for an interview, I recommend typing out L3 and L4 code yourself — these two are what interviewers love to dig into, and they're the most valuable part of this article. If you find it useful, bookmark it so you don't lose it.

4. Layered implementation: from "works" to "handles high traffic"

L0 Emergency troubleshooting: confirm the SQL is taking the right path

Don't rush to architecture; three steps:

-- 1) Check the execution plan: is it using the smallest secondary index?
EXPLAIN SELECT COUNT(*) FROM t_order;

-- 2) If there's no suitable secondary index, create a narrow "index built for counting"
--    Put the filter column first in a composite index, append the primary key at the end, try to make it a covering index (Using index)
ALTER TABLE t_order ADD INDEX idx_status_id (status, id);

-- 3) Add a timeout guard to slow SQL so it doesn't kill the database
SELECT /*+ SET_VAR(max_execution_time = 60000) */ COUNT(*) FROM t_order;

-- 4) MySQL 8.0.14+ parallel scan (emergency tactic, not a routine solution)
SET SESSION innodb_parallel_read_threads = 4;
EXPLAIN ANALYZE SELECT COUNT(*) FROM t_order;

⚠️ Hard prerequisites for 8.0 parallel scan, all required: no WHERE / no GROUP BY / no ORDER BY / no LIMIT, a normal non-partitioned InnoDB table, must use clustered index scan (having a secondary index disables parallelism), and enough data. To verify whether it's truly parallel, only EXPLAIN FORMAT=TREE showing Parallel scan works; plain EXPLAIN won't show it. In production this is a "narrow lane" capability; the thread pool is still shared, so under high concurrency everyone fighting for threads can make it slower instead. Don't rely on it as a normal solution.

Another piece of experience: a large-table count is a long large scan that can evict hot data from the buffer pool. So use a replica if possible, run at low peak if possible, and don't hammer the primary during peak hours.

L1 Only need magnitude: approximate estimate, millisecond return

For admin dashboards, capacity assessment, trend charts, nobody cares whether it's 12,345,678 or 12,340,000:

-- Method 1: optimizer statistics
SELECT TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mall' AND TABLE_NAME = 't_order';

-- Method 2: simpler, look at the Rows column
SHOW TABLE STATUS LIKE 't_order';

-- 8.0 has a 24-hour statistics cache by default; turn it off temporarily for fresher values
SET SESSION information_schema_stats_expiry = 0;

The principle is InnoDB sampling pages to estimate — persistent statistics sample only 20 pages by default, with error up to 40%~50%, and just after deleting 100k rows it may still show the old value. ✅ Usable for: dashboards, trends, capacity assessment. ❌ Not usable for: pagination totals, financial reconciliation, business branch decisions.

For billion-level tables, there are wilder routes: query information_schema.partitions per partition and sum up estimates; or maintain a 1:1000 sample table and do SELECT COUNT(*) * 1000 FROM user_sample, usually within ±5% error.

L2 Exact but low frequency: parallel COUNT by primary key sharding

For scenarios like nightly report generation or ad-hoc reconciliation, if a single thread can't finish scanning, split by primary key range, scan in parallel across multiple threads, then sum. 30 seconds drops to 3 seconds, a 3~10x performance improvement:

public long parallelCount(String tableName, long shardSize) throws Exception {
    // First get primary key boundaries
    Long minId = jdbcTemplate.queryForObject("SELECT MIN(id) FROM " + tableName, Long.class);
    Long maxId = jdbcTemplate.queryForObject("SELECT MAX(id) FROM " + tableName, Long.class);
    if (minId == null || maxId == null) return 0;

    // Construct shard tasks; each shard's id range recommended 100k~1 million
    List<Callable<Long>> tasks = new ArrayList<>();
    for (long start = minId; start <= maxId; start += shardSize) {
        long end = Math.min(start + shardSize - 1, maxId);
        String sql = String.format("SELECT COUNT(*) FROM %s WHERE id >= ? AND id <= ?", tableName);
        tasks.add(() -> jdbcTemplate.queryForObject(sql, Long.class, start, end));
    }

    // Use a dedicated thread pool for parallel execution + summation (don't exhaust the business thread pool)
    return countExecutor.invokeAll(tasks).stream()
            .mapToLong(future -> {
                try { return future.get(); }
                catch (InterruptedException | ExecutionException e) {
                    throw new RuntimeException("Shard count failed", e);
                }
            }).sum();
}

Several points learned from practice:

Issue Solution
Primary key has holes (deleted data) Segmented ranges are still correct; some shards just scan fewer rows
Uneven data distribution Sample primary key boundaries first, then split by boundaries
Concurrency hammering the database Control thread count, use read-only replicas, add rate limiting between shards
Writes during statistics Accept temporary inconsistency, or use a snapshot / off-peak execution

L3 Exact + high frequency: bucket counter table (strong consistency, the standard big-company answer) ⭐

If the page displays frequently and must be exact, you can't count on the fly each time — turn O(N) "counting" into O(1) "reading", maintaining the count during writes.

Why buckets? A single-row counter under high-concurrency writes makes all requests compete for the same row lock, creating a hot spot. Splitting into 16~64 buckets spreads the lock contention; reading is a SUM that still returns an exact value in milliseconds. Don't make too many buckets; too many slows down the SUM.

CREATE TABLE t_entity_count (
  biz_name   VARCHAR(64)       NOT NULL COMMENT 'business identifier, e.g. order_total',
  bucket_id  SMALLINT UNSIGNED NOT NULL COMMENT 'bucket number 0..63',
  cnt        BIGINT            NOT NULL DEFAULT 0,
  PRIMARY KEY (biz_name, bucket_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Write: atomic increment, avoids "check-then-update" lost updates
-- 8.0.19+ recommended row alias syntax (VALUES() deprecated since 8.0.20)
INSERT INTO t_entity_count (biz_name, bucket_id, cnt)
VALUES ('order_total', ?, ?)
AS new
ON DUPLICATE KEY UPDATE cnt = t_entity_count.cnt + new.cnt;

-- Read: SUM of 64 rows, millisecond-level and exact
SELECT SUM(cnt) FROM t_entity_count WHERE biz_name = 'order_total';

The key on the Java side is to use the same database and same transaction as the business write, giving natural strong consistency — if the business rolls back, the count rolls back with it:

private static final int BUCKET_NUM = 64;

@Transactional(rollbackFor = Exception.class)
public void createOrder(Order order) {
    orderMapper.insert(order);                        // 1. write business table
    int bucket = bucketOf(order.getOrderId());        // 2. increment count within same transaction
    countMapper.incr("order_total", bucket, 1L);
}

/** Bucket mapping algorithm must be identical to the reconciliation SQL */
private int bucketOf(long id) {
    CRC32 crc = new CRC32();                          // same polynomial as MySQL CRC32()
    crc.update(Long.toString(id).getBytes(StandardCharsets.US_ASCII));
    return (int) (crc.getValue() % BUCKET_NUM);
}

🔥 High-frequency pitfall: using Java hashCode() for bucketing and SQL CRC32 for reconciliation — with different algorithms, the calibration task scatters counts and never reconciles. The bucketing algorithm must be unique and shared on both sides. Also use a fixed orderId modulo rather than random number bucketing — when deleting, you can find the same bucket and subtract 1; with random bucketing, the deletion side can't match.

Two engineering details not to miss:

// For batch import, don't loop N times with UPDATE; first compute delta Δ, only 1 count SQL for 100k imports
long delta = batch.size();
countMapper.incr("order_total", bucketOf(firstId), delta);
-- Daily off-peak full calibration: use the business table's real value to overwrite the counter table
UPDATE t_entity_count c
JOIN (
  SELECT MOD(CRC32(CAST(id AS CHAR)), 64) AS bucket_id, COUNT(*) AS real_cnt
  FROM t_order
  GROUP BY 1
) s ON s.bucket_id = c.bucket_id
SET c.cnt = s.real_cnt
WHERE c.biz_name = 'order_total';

A counting system doesn't fear being slow, it fears silently drifting off. Daily calibration is the last gate against drift.

L4 High throughput: Redis counter + compensation + reconciliation

When read pressure is too high even for a counter table, put Redis as a projection layer. First remember the iron rule: DB is the single source of truth; Redis is just an acceleration cache and can be rebuilt — if Redis goes down, rebuild from DB. Never make the business unavailable because the cache was lost.

@Service
public class OrderCountService {

    @Autowired private StringRedisTemplate redis;
    @Autowired private OrderMapper orderMapper;
    @Autowired private CompensateMapper compensateMapper;

    private static final String COUNT_KEY = "order:count";

    /**
     * ✅ New order: after MySQL transaction commits, INCR Redis
     * Key point 1: don't use GET + SET (non-atomic, will lose data under concurrency), must use INCR/DECR
     * Key point 2: must write in afterCommit, otherwise Redis increments but MySQL rolls back -> dirty data
     */
    @Transactional(rollbackFor = Exception.class)
    public void createOrder(Order order) {
        orderMapper.insert(order);
        TransactionSynchronizationManager.registerSynchronization(
            new TransactionSynchronization() {
                @Override
                public void afterCommit() {
                    try {
                        redis.opsForValue().increment(COUNT_KEY);
                    } catch (Exception e) {
                        // Write failure goes to compensation table; background task retries, so count never silently lost
                        compensateMapper.insert(new CompensateTask(COUNT_KEY, 1L));
                    }
                }
            });
    }

    /**
     * ✅ Soft delete scenario: DELETE becomes UPDATE, must DECR, otherwise count stays inflated and never self-heals
     */
    @Transactional(rollbackFor = Exception.class)
    public void deleteOrder(Long id) {
        orderMapper.softDelete(id);   // UPDATE SET deleted = 1
        TransactionSynchronizationManager.registerSynchronization(
            new TransactionSynchronization() {
                @Override
                public void afterCommit() {
                    redis.opsForValue().decrement(COUNT_KEY);
                }
            });
    }

    /**
     * ✅ Query: cache first, on miss fall back to DB + mutual-exclusion rebuild to prevent cache stampede
     */
    public long getOrderCount() {
        String val = redis.opsForValue().get(COUNT_KEY);
        if (val != null) return Long.parseLong(val);
        synchronized (this) {                  // single-machine lock; use SETNX in distributed scenario
            val = redis.opsForValue().get(COUNT_KEY);
            if (val != null) return Long.parseLong(val);
            Long db = orderMapper.countAll();  // prefer replica
            redis.opsForValue().set(COUNT_KEY, String.valueOf(db), 1, TimeUnit.HOURS);
            return db;
        }
    }

    /**
     * 🔄 Daily full calibration at 3am: catches cumulative drift from INCR/DECR, also safe if Redis crashes and loses data
     */
    @Scheduled(cron = "0 0 3 * * ?")
    public void calibrate() {
        Long real = orderMapper.countAll();    // full-table count at low peak
        redis.opsForValue().set(COUNT_KEY, String.valueOf(real));
    }
}

This "double-write + compensation + calibration" trio is paid for with pitfalls:

L5 Zero business intrusion: Canal listens to binlog for async counting

Can't modify business code in a legacy system? Work from the binlog:

image.png

@KafkaListener(topics = "binlog_t_order")
public void onMessage(String payload) {
    RowChange row = JSON.parseObject(payload, RowChange.class);
    // Unique index conflict = already consumed, drop (MQ at-least-once semantics require idempotency)
    if (!idempotentMapper.tryInsert(row.getGtid() + ":" + row.getOffset())) {
        return;
    }
    long delta = switch (row.getEventType()) {
        case INSERT -> 1L;
        case DELETE -> -1L;
        default -> 0L;   // UPDATE on primary key doesn't affect total count
    };
    redisTemplate.opsForValue().increment("count:order:total", delta);
}

The advantage is zero business code changes, and it captures both inserts and deletes while also syncing to ES or a data warehouse. Three difficulties: duplicate consumption is solved with an idempotency table (GTID + offset unique index), otherwise counts only keep growing; logical deletes need an UPDATE branch that compares before/after images to compute delta (status: normal→deleted means -1, reverse +1); UPDATE changing the primary key needs special handling, or you'll over-count or under-count.

L6 Pagination scenario: the best solution is often "no total"

Honestly, many count requirements are fake needs from the start:

// ❌ count on every page flip, LIMIT 100000, 20 also causes deep offset, doubly slow
// ✅ Option A: cursor pagination (load more), no total at all
List<Order> list = orderMapper.selectByCursor(lastId, size);

// ✅ Option B: total goes through cache and is counted once, no recalculation on page flips
long total = countCache.getOrCompute("order_total");

// ✅ Option C: threshold truncation, beyond 10k show "10000+" or "approximately 12 million"

What users really need isn't data from page ten thousand, but "about how many are there". Downgrading exact totals to approximate totals, or simply switching to infinite scroll "load more", is the cheapest optimization — especially for admin systems. If the product side can solve it, don't leave it to the database to bear.

5. Going bigger: sharded databases and OLAP

Once a single table exceeds 100 million rows, all single-database solutions degrade continuously. A few paths:

The core idea doesn't change: turn full-table scans into incremental maintenance, just moving the maintenance location from the single database to an external system. Cross-database scenarios require accepting that the counter table and business table can't be in the same transaction, so only eventual consistency + reconciliation is possible.

6. Pitfall checklist: 12 quick-reference issues

# Issue Root cause / symptom Solution
1 Large-table COUNT(*) slow MVCC has no global row count; checks visibility per row, O(N) scan Narrow secondary index to reduce range; parallel sharding; external counting system
2 count(field) especially slow NULL check + index not covering leads to table lookups Use count(*); add covering index if necessary
3 Estimate value unreliable Samples 20 pages, error 40%+ Only for dashboards; never trust estimates for business logic
4 Single-row counter hot spot High concurrency competes for same row lock Bucket + SUM with ON DUPLICATE KEY UPDATE
5 Soft-delete count drift DELETE changed to UPDATE, count only increases Sync DECR on delete path + daily full calibration
6 Redis and DB inconsistent Write failures, crashes, expiration data loss afterCommit double-write + compensation table retry + scheduled reconciliation + AOF
7 binlog duplicate consumption MQ at-least-once semantics Idempotency table (GTID + offset unique index)
8 Batch import / TRUNCATE Per-row counting missed or performance collapses Compute Δ and update once; rebuild count after TRUNCATE
9 Bucket algorithm mismatch Calibration scatters counts, never reconciles Standardize on one bucket algorithm (Java CRC32 ↔ MySQL CRC32)
10 8.0 parallel not effective Has secondary index / WHERE / GROUP BY Verify with EXPLAIN FORMAT=TREE, don't trust the parameter blindly
11 Replica lag reads stale value Replica data not synced Read count from primary or Redis, not replica
12 Using count for existence check RR snapshot read without locks, concurrent duplicate inserts Unique index + INSERT IGNORE / ON DUPLICATE KEY

Two common misconceptions: SELECT MAX(id) as total row count is wrong — ids have holes; max id of 1000 could mean only 6 rows. For initializing historical data counts, run one COUNT(*) during low peak and inject it, then go incremental. For large-table DDL like adding that narrow index, use pt-online-schema-change or gh-ost to alter online, don't lock the table.

7. Interview follow-up rehearsal (these questions are the most likely)

Q: Does count(*) take locks? Under RR, it's a consistent snapshot read, no row locks, no blocking of writes; but a long large-scan can fill the buffer pool and evict hot data, so large-table count should go to a replica.

Q: How does a counter table stay consistent with business data? Same database, same transaction update, ON DUPLICATE KEY UPDATE atomic increment, rollback together; plus daily calibration to cover historical drift. Cross-database can't share a transaction, so only eventual consistency.

Q: What if Redis and DB are inconsistent? DB is the source of truth. Write-path failures go to a compensation table, read path accepts second-level error, daily full calibration resets. This is "eventual consistency", don't promise strong consistency.

Q: What else new in MySQL 8.0 can I talk about? 8.0.14+ parallel scan (strict prerequisites, treat as emergency); histogram statistics can greatly improve estimate accuracy for conditional counts; information_schema_stats_expiry controls stats cache freshness. Get to this level and you're basically solid.

Final words

The whole problem compresses into three sentences: estimate when you can, async when you can, incremental when you can. In detail — first see if the requirement can be downgraded to approximate values or load-more; if exact is required, use a bucket counter table maintained in the same transaction as business; if read pressure is higher, add Redis as a projection, a compensation table against loss, binlog as a fallback, and daily reconciliation.

Implementation suggestion: start with L0's EXPLAIN check, zero cost; if you really need a counting system, prioritize L3 bucket counter table (strong consistency, few dependencies), then add L4 Redis when reads can't be handled. Remember, a counting system doesn't fear being slow, it fears silently drifting, and calibration and compensation always take priority over performance.

Last interaction: how does your project handle "total count"? Direct count, or a counter table/Redis? Have you stepped on the soft-delete count inflation trap? Comment below. If this gets 500 likes, I'll write the follow-up "Full implementation of hot-spot counter table bucketing + Redis Lua for atomicity" deep dive.


For more MySQL practice, interview breakdowns, and pitfall records, follow my WeChat account [Rain的Java大神之路], updated weekly.

Comments

Top 1 of 2 from juejin.cn, machine-translated. The original thread is authoritative.

汪汪队首席上单

How to analyze?

Rain的Java大神之路

Hello, the core is actually to first grasp the business, don't just run count head-on. InnoDB is affected by MVCC, so every count(*) scans the index for visibility checks and inherently cannot get a stable row count directly. Three points: whether you need an exact value, how high the real-time requirement is, and whether the query has a WHERE condition. If you can accept error, go with statistics-based estimation; if you need exact but not high-frequency, use partitioned parallel counting; for high-frequency real-time, use a counter table or Redis, and remember to schedule reconciliation and calibration, otherwise the numbers will slowly drift. The article has examples for each option; just tell me which part you don't understand.