How to optimize slow SELECT COUNT(*) queries on large MySQL InnoDB tables?

InnoDB does not store table or index row counts; it has to traverse the secondary index pages to count matching records. On millions of rows, that hurts.

Here are the industry standard solutions:
1. Counter Caching (Denormalization): Add a posts_count column directly to the parent community table. Increment on INSERT, decrement on DELETE. Query time drops from 450ms to 0.5ms.
2. Redis caching: Store counts in a Redis hash with atomic HINCRBY / HDECRBY.
3. Approximate counts for pagination: If exact count isn't critical (like Google search pagination), use EXPLAIN SELECT ... to get an estimated row count instantly.

Counter caching directly on the parent row is by far the cleanest. We implemented this with simple posts_count = posts_count + 1 in our feed service and it reduced DB server load by 70%.

Wait for the ending! 🔥