Improving COUNT(*) Performance on Large InnoDB Tables by Adding a Secondary Index

Background: A project required counting rows in a ~5 million‑row InnoDB table api_runtime_log, but SELECT COUNT(*) took over 40 seconds.

Original analysis showed the query used the primary (clustered) index, causing the entire index (≈10 GB) to be read into the buffer pool.

Principle: In InnoDB, COUNT(*) first reads an index into memory; if a secondary index exists, the optimizer prefers the smallest one.

Solution: Create a secondary index on the column used in the count (e.g., rowguid). After adding the index, the query finished in under a second and the execution plan switched to the secondary index.

Verification: Re‑run the query and EXPLAIN before and after adding the index, confirming the plan change and the reduced buffer‑pool usage.

Deep testing with sysbench on a 500 M‑row table demonstrated that counting via the primary index loads the whole table into memory, while counting via a secondary index loads only the index pages (tens of MB).

Conclusion: When COUNT(*) scans the primary index, performance degrades on large tables; adding an appropriate secondary index dramatically speeds up the operation.

Optimization suggestions include using triggers with a summary table, switching to MyISAM, ETL to other systems, or upgrading to MySQL 8 with parallel query.