MySQL Tuning, Indexing & Concurrency
5-8 Years (Senior)
Medium
Production Scenario
Why does `SELECT COUNT(*)` on InnoDB scan the entire primary key tree unlike MyISAM?
Real-World Scenario / Incident Description
Executing `SELECT COUNT(*) FROM orders` on a 50-million row InnoDB table took 12 seconds.
767 views
•
Updated 1 month ago
Why InnoDB Cannot Store an Exact Row Count
Because InnoDB uses MVCC (Multi-Version Concurrency Control), different concurrent transactions see different numbers of visible rows at the exact same moment.
- MyISAM locked tables and stored a single counter, but did not support transactions.
- Solution for Large Tables: Use Redis cached counters or query
information_schema.TABLES.TABLE_ROWSfor approximations.
Related Concepts:
#COUNT
#InnoDB
#MVCC
#Performance
More MySQL Tuning, Indexing & Concurrency Interview Questions
How does the InnoDB B+Tree Index work internally, and why does Leftmost Prefix Rule dictate composite index column ordering?
Read →
Explain InnoDB Locking Modes: Record Locks, Gap Locks, and Next-Key Locks, and how they prevent Phantom Reads.
Read →
How do you optimize deep pagination (`SELECT * ... LIMIT 5000000, 20`) on large tables with 100M rows?
Read →
How do you interpret MySQL `EXPLAIN ANALYZE` output to diagnose `Using filesort` and `Using temporary`?
Read →
Explain MySQL Table Partitioning (Range, List, Hash) vs. Application-Level Sharding for 1TB+ databases.
Read →