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
countwith aWHEREcondition 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.
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":
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=TREEshowingParallel scanworks; plainEXPLAINwon'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 SQLCRC32for 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 fixedorderIdmodulo 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:
afterCommit()ensures DB write first, then count; reversing the order produces dirty data;- The soft-delete trap is where 90% of people fall —
DELETEchanged toUPDATE deleted=1, forgetting to DECR, the number keeps climbing and is very hard to debug. Before going live, stress-test the soft-delete flow; - To keep multiple keys consistent (like total + today's new count), use a Lua script to ensure atomicity, not two separate commands;
- On the Redis side, enable AOF/master-slave, and set a drift threshold for the calibration task before writing back, to avoid frequent useless writes.
L5 Zero business intrusion: Canal listens to binlog for async counting
Can't modify business code in a legacy system? Work from the binlog:
@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:
- Option A: each shard runs parallel
COUNT(*), the application layer aggregates in memory (ShardingSphere supports this natively); - Option B: Canal listens to binlog from all shards and feeds insert/delete events to a unified counter table / Redis;
- Option C: hot/cold data separation, archive cold data; for multi-dimensional complex statistics, use ClickHouse / ES OLAP engines, or Flink real-time data warehouse;
- Option D: if a single machine truly can't handle it, switch to TiDB, a natively distributed database.
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.
Top 1 of 2 from juejin.cn, machine-translated. The original thread is authoritative.
How to analyze?
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.