Unqualified COUNT(*) speed: PBXT vs InnoDB
SELECT COUNT(*) FROM t with no WHERE forces a PK scan on MVCC engines. Cache ballpark figures; do not run it on every page view.
SELECT COUNT(*) FROM tbl with no WHERE is cheap on MyISAM (table-level row counter). InnoDB and other MVCC engines cannot keep one global count: different transactions may see different row sets.
What the engine does
InnoDB walks the primary key (or a covering secondary index when lucky) and tallies visible rows. Cost scales with table size and how much of that index is already in the buffer pool.
PBXT historically showed a useful edge on this path versus InnoDB on large tables — interesting for reporting workloads where you might otherwise park a MyISAM copy. Absolute wall times on a sick host are meaningless; the qualitative lesson is not.
What to do instead
- Frontends almost never need an exact live count. Cron a count into memcached, Redis, or a summary table.
- With a
WHERE, every engine is in the same boat: index selectivity decides cost. - A replica may use a different engine than the primary for specialised reporting.
If an unqualified count takes tens of minutes on tens of millions of rows, fix I/O and buffer pool before blaming the SQL dialect.