Why MySQL COUNT(*) Can Be Milliseconds Fast: Engine Tricks & Optimization Strategies

Background

During technical interviews a candidate was asked to demonstrate SQL for counting rows in a massive MySQL table. The user table contains over 30 million rows; a full‑table COUNT(*) on InnoDB took 14.8 seconds.

After switching the table to the MyISAM storage engine, the same query completed in less than one millisecond.

Why the difference?

InnoDB must read each row to compute COUNT(*), whereas MyISAM stores the total row count in its metadata and can return it directly. InnoDB can also use optimizations when a secondary index exists, scanning the index instead of the full table.

Optimizing COUNT(*) on large InnoDB tables

Several practical solutions are presented:

Redis incremental counter – Increment a Redis key on each INSERT and read the key for instant counts. This is fast but introduces cache‑DB consistency concerns.

MySQL counting table + transaction – Maintain a separate table that stores the row count and update it within the same transaction as the main INSERT, ensuring accurate counts without extra infrastructure.

MySQL counting table + trigger – Create an AFTER INSERT trigger on the main table to update the counting table automatically, removing the need for application‑level transaction code.

Increase parallel read threads – MySQL 8.0.14 introduced the innodb_parallel_read_threads parameter (default 4). Setting it to 16 reduced the count time from 14.8 s to 6.1 s; further increasing to 32 gave a marginal improvement (6.8 s) before I/O became the bottleneck.

Add a secondary index – Adding a minimal secondary index (e.g., on the sex column) allows MySQL to satisfy COUNT(*) via an index scan, cutting the execution time to about 10.6 s in the test case.

SHOW TABLE STATUS / EXPLAIN – These commands can retrieve an estimated row count quickly. In the demonstrated case the estimate differed by roughly 1 % from the actual count.

Code examples

CREATE TRIGGER `user_count_trigger` AFTER INSERT ON `user`
FOR EACH ROW
BEGIN
    UPDATE user_count SET count = count + 1 WHERE id = NEW.id;
END

SET LOCAL innodb_parallel_read_threads = 16;

Observations

Redis provides the fastest read‑only count but requires careful handling of consistency between cache and database. The MySQL counting‑table approaches keep data accurate and avoid extra services, though they may become a performance bottleneck under very high concurrency. Parallel read threads improve performance up to the point where I/O limits are reached. Adding a small secondary index yields noticeable speedups without any code changes. SHOW TABLE STATUS and EXPLAIN give quick, albeit approximate, row counts.