Skip to content
MySQL Large Table COUNT(*) Optimization: 6-Layer Solution from MVCC to Bucket Counting
Databases 27 min read

MySQL Large Table COUNT(*) Optimization: 6-Layer Solution from MVCC to Bucket Counting

This article explains why MySQL COUNT(*) is slow on large tables due to MVCC, and presents a six-layer optimization strategy from emergency indexing to bucket counting tables, Redis caching, and binlog-based async counting, with code examples and 12 pitfalls to avoid.

Rain's Path to Java Mastery
Rain's Path to Java Mastery
Rain's Path to Java Mastery

Introduction

A real-world scenario: an 80-million-row order table, a simple SELECT COUNT(*) FROM t_order takes 8 seconds, causing interface timeout. In interviews, candidates often answer "use count(1) instead of count(*)" or "add a cache", which misses the core issue. This question tests requirement clarification, architecture selection, and consistency guarantees.

Core Principles

The Impossible Triangle

Precise, real-time, large table — pick at most two. Accept approximation for millisecond response; for precise high-frequency counts, move counting out of the database.

Why InnoDB COUNT(*) Is Slow

Unlike MyISAM which stores row count in metadata (O(1)), InnoDB uses MVCC. Each transaction sees a different row count based on its ReadView, so InnoDB must scan an index tree, check visibility per row, and accumulate. This is O(N) and scales linearly with data volume.

Index Selection for COUNT(*)

InnoDB automatically picks the smallest secondary index to scan. A narrow secondary index (e.g., tinyint status) stores only index columns + primary key, fitting more entries per 16KB page, reducing I/O. Adding a dedicated narrow index can speed up COUNT(*) 5-10x. Verify with EXPLAIN SELECT COUNT(*) FROM t_order; look for type: index, key: idx_status, Extra: Using index.

COUNT(*) vs COUNT(1) vs COUNT(id) vs COUNT(column)

In MySQL 5.7/8.0 InnoDB, COUNT(*) and COUNT(1) have identical execution plans; "count(1) is faster" is an Oracle-era myth. COUNT(primary_key) is slightly slower due to value retrieval. COUNT(column) counts non-NULL values only, may cause table lookup if index doesn't cover, and is slowest. Always prefer COUNT(*).

Don't Use COUNT for Concurrency Deduplication

Under Repeatable Read, SELECT COUNT(*) WHERE phone = '138xxxx' is a non-locking snapshot read. Two concurrent transactions both see 0, then both insert, creating duplicates. Use unique index + INSERT ... ON DUPLICATE KEY UPDATE or INSERT IGNORE instead.

Six-Layer Solutions (L0-L6)

L0: Emergency Triage

Check execution plan with EXPLAIN; ensure smallest secondary index is used.

Add a dedicated narrow index (e.g., ALTER TABLE t_order ADD INDEX idx_status_id (status, id)).

Set query timeout:

SELECT /*+ SET_VAR(max_execution_time = 60000) */ COUNT(*) FROM t_order

.

MySQL 8.0.14+ parallel scan: SET SESSION innodb_parallel_read_threads = 4. Strict prerequisites: no WHERE/GROUP BY/ORDER BY/LIMIT, non-partitioned InnoDB table, must use clustered index scan (secondary indexes disable parallel scan). Verify with EXPLAIN FORMAT=TREE for Parallel scan. Not for routine use.

Run on replica during off-peak to avoid evicting hot data from buffer pool.

L1: Approximate Estimation (Millisecond Response)

Use optimizer statistics:

SELECT TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA='mall' AND TABLE_NAME='t_order'

or SHOW TABLE STATUS LIKE 't_order'.

Based on sampling 20 pages; error up to 40-50%. Suitable for dashboards, capacity planning, trends. Not for pagination totals, financial reconciliation, or business branching.

For billion-row tables: query information_schema.partitions per partition, or maintain a 1/1000 sample table.

L2: Exact but Low-Frequency — Primary Key Shard Parallel COUNT

Split by primary key ranges, run parallel COUNT(*) per shard, sum results. 30s → 3s (3-10x speedup).

Java example: fetch min/max ID, create tasks per shard (100k-1M rows), execute with dedicated thread pool, aggregate futures.

Handle primary key gaps (deleted data), data skew (sample boundaries first), DB overload (limit threads, use read replica, rate limit), concurrent writes (accept brief inconsistency or run at low peak).

L3: Exact + High-Frequency — Bucket Counting Table (Strong Consistency) ⭐

Convert O(N) counting to O(1) read by maintaining counts at write time. Single counter row becomes hotspot under high write concurrency; split into 16-64 buckets to distribute lock contention. Read sums buckets ( SUM(cnt)) — still exact, millisecond latency.

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 0..63',  cnt BIGINT NOT NULL DEFAULT 0,  PRIMARY KEY (biz_name, bucket_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Write (atomic increment, MySQL 8.0.19+ alias syntax):

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:

SELECT SUM(cnt) FROM t_entity_count WHERE biz_name = 'order_total';

Java: same transaction as business write (strong consistency — rollback rolls back count). Bucket mapping uses CRC32 (matching MySQL's CRC32()) on orderId, not hashCode(). Critical: bucket algorithm must be identical in Java and SQL calibration; otherwise calibration scatters counts irrecoverably. Use fixed orderId modulo, not random, so deletes can find the same bucket to decrement.

Batch import: compute delta, single incr call. Daily calibration: overwrite bucket counts from actual table using same CRC32 bucketing.

L4: High Throughput — Redis Counting + Compensation + Calibration

DB is source of truth; Redis is rebuildable cache. Three-piece suite:

Write path: after MySQL transaction commits ( TransactionSynchronizationManager.registerSynchronization with afterCommit()), INCR Redis. On failure, write to compensation table for retry.

Soft delete: UPDATE deleted=1 must DECR Redis; missing this causes perpetual overcount (90% pitfall).

Read: cache-first, on miss use mutex (single-node synchronized, distributed SETNX) to rebuild from DB (prefer replica).

Daily calibration job: full COUNT(*) from DB (off-peak), overwrite Redis key.

Multiple keys (total + daily new) — use Lua script for atomicity.

Enable AOF/replication; calibration writes only if diff exceeds threshold.

L5: Zero Business Intrusion — Canal Binlog Listener

For legacy systems where business code cannot change. Canal parses binlog, publishes to Kafka. Consumer:

Idempotency: unique index on GTID+offset to discard duplicates (MQ at-least-once).

Event mapping: INSERT → +1, DELETE → -1, UPDATE (primary key unchanged) → 0. Logical delete (status normal→deleted) → -1, reverse → +1. UPDATE changing primary key needs special handling.

Increment Redis directly.

L6: Pagination — Often the Best Solution Is "Don't Need Total"

Cursor-based pagination (load more) eliminates total count.

Cache total once, reuse for subsequent pages.

Threshold truncation: show "10000+" or "~12M" beyond 10k.

Users rarely need exact total; approximate or infinite scroll is cheaper.

Sharding & OLAP

Shard-parallel COUNT(*) with in-memory aggregation (ShardingSphere supports).

Canal across all shards → unified counting table/Redis.

Cold/hot separation; complex multi-dimensional stats → ClickHouse/ES/Flink.

Migration to distributed DB like TiDB.

Core idea: turn full scan into incremental maintenance, just moved to external systems. Cross-shard counting loses same-transaction guarantee → eventual consistency + reconciliation.

12-Pitfall Checklist

1. Large table COUNT(*) slow — MVCC requires per-row visibility check, O(N) scan. Solution: narrow secondary index; shard parallel; external counting system.

2. count(column) especially slow — NULL check + possible table lookup. Solution: use count(*); add covering index if needed.

3. Estimation unreliable — 20-page sampling, 40%+ error. Solution: only for dashboards; never trust for business logic.

4. Counter single-row hotspot — High concurrency contends same row lock. Solution: bucketed sum + ON DUPLICATE KEY UPDATE.

5. Soft delete count drift — DELETE becomes UPDATE, counter only increments. Solution: sync DECR on delete path + daily full calibration.

6. Redis vs DB inconsistency — Write failure, crash, expiration loss. Solution: afterCommit dual-write + compensation table retry + scheduled reconciliation + AOF.

7. Binlog duplicate consumption — MQ at-least-once semantics. Solution: idempotency table (GTID+offset unique index).

8. Bulk import / TRUNCATE — Per-row counting misses or kills performance. Solution: compute delta, single update; rebuild counter after TRUNCATE.

9. Bucket algorithm mismatch — Calibration scatters counts, never matches. Solution: unify algorithm (Java CRC32 ↔ MySQL CRC32).

10. 8.0 parallel scan not working — Secondary index exists / WHERE / GROUP BY present. Solution: verify with EXPLAIN FORMAT=TREE for Parallel scan.

11. Replica lag reads stale value — Replica not caught up. Solution: route counting to primary or Redis, not replica.

12. Using count for existence check — RR snapshot read doesn't lock, concurrent duplicate inserts. Solution: unique index + INSERT IGNORE / ON DUPLICATE KEY.

Additional notes: SELECT MAX(id) ≠ row count (ID gaps). Initialize historical counts with one-off COUNT(*) off-peak, then incremental. For DDL on large tables (e.g., adding narrow index), use pt-online-schema-change or gh-ost to avoid locking.

Interview Follow-Up Questions

Q: Does COUNT(*) lock? RR uses consistent snapshot read, no row locks, no write blocking. But long scan fills buffer pool, evicting hot data → run on replica.

Q: How to keep counting table consistent with business data? Same DB, same transaction, atomic ON DUPLICATE KEY UPDATE, rollback together. Daily calibration catches historical drift. Cross-DB → eventual consistency only.

Q: Redis-DB inconsistency? DB is truth. Write failures go to compensation table for retry. Reads tolerate seconds-level staleness. Daily full calibration resets. This is eventual consistency — never promise strong consistency.

Q: MySQL 8.0 new features? 8.0.14+ parallel scan (strict prerequisites, emergency only); histogram statistics improve conditional count estimation; information_schema_stats_expiry controls stats cache TTL.

Conclusion

Three-sentence mantra: Estimate if possible, async if possible, incremental if possible. First, see if requirement can downgrade to approximation or cursor pagination. If exact needed, use bucket counting table with same-transaction maintenance. If read pressure high, add Redis projection with compensation, binlog fallback, daily reconciliation. Remember: counting systems aren't afraid of being slow, they're afraid of silently drifting out of sync — calibration and compensation always trump performance optimization.

Original Source

Signed-in readers can open the original source through BestHub's protected redirect.

Sign in to view source
Republication Notice

This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactadmin@besthub.devand we will review it promptly.

MySQLbinlogCanalinterview preparationRedis cachingMVCCCOUNT optimizationbucket counting
Reader feedback

How this landed with the community

Sign in to like

Rate this article

Was this worth your time?

Sign in to rate
Discussion

0 Comments

Thoughtful readers leave field notes, pushback, and hard-won operational detail here.