Why is SELECT COUNT(*) slow on huge tables and what are the alternatives?

Answer preview

SELECT COUNT(*) on a huge table must visit every visible row (or every index page for an index-only count) — there is no magic shortcut. At billions of rows it becomes a full-table scan that blocks nothing but still costs seconds.…