Stop Tuning COUNT(*): Ask What the Number Is For, Then Move It Out of the Table
A single slow COUNT(*) can time out a dashboard or an order list, and most optimization attempts only move the bottleneck. Asking for precision and real-time first, then moving counting into a bucket table or Redis, keeps the query from ever scanning 80 million rows.
Counting rows in an 80-million-row InnoDB table is a classic interview question, and the first correct step is not to write SQL but to ask what the total is for. Precision, real-time, and table scale form an impossible triangle: you get at most two. If an approximate magnitude works, the optimizer's statistics table returns in milliseconds; if you need exact and frequently read, the count moves out of the table into a bucket counter or Redis projection.
Six levels of solutions run from a cheap EXPLAIN and a purpose-built narrow secondary index to parallel ID-shard counting, a 16-64 bucket counter table updated in the same transaction, a Redis counter with after-commit increments and a compensation table, and a Canal binlog listener that needs idempotency for at-least-once messaging. Each level carries code and pitfalls, including the classic soft-delete trap where DELETE becomes UPDATE and the counter drifts upward, and the requirement that Java and MySQL agree on the same bucket hash function.
The final advice: estimate when you don't need exact, async when you don't need real-time, and incremental instead of full scans. For pagination, the cheapest fix is to drop the total and use cursor-based load-more. A daily calibration against the real table is the safety net every counting system needs, because counts don't fail loudly; they drift silently.
The first question in any COUNT optimization should be about tolerance for error, not index size. That requirement clarity decides whether you spend an afternoon on a bucket table or five minutes on an estimate.
The soft-delete drift trap is the most commonly missed failure mode because it doesn't error; it silently inflates. Any counting system must cover every mutation path, including UPDATE-based deletes.
Moving COUNT into a bucket counter shifts cost from reads to writes, but the daily calibration is what keeps it honest. Without that, any missed write or edge case accumulates forever.
The advice to replace total count with cursor pagination is the cheapest and most effective fix, but it requires product agreement. Most developers won't propose it because they assume the page-number metric is non-negotiable.
The claim that COUNT(1) and COUNT(*) are identical is correct and backed by MySQL 8.0 official docs, but the interview question still ignores it because the real bottleneck is scanning 80 million rows, not the function.