Why is returning a total count with every page expensive, and what are the alternatives?
COUNT(*) over a large filtered table scans many rows on every request. Alternatives are returning only has_next, using approximate counts, caching the count, or showing 'more than 1,000' instead of an exact total.
Explain it simply
Many UIs show "Page 3 of 52" or "12,430 results". To produce that number, the database runs SELECT COUNT(*) with the same filters, which on millions of rows may read a huge part of an index or table. And you do it again for every page request, even though the page itself needs just 20 rows.
Cheaper options:
- has_next flag: fetch limit + 1 rows; if you get the extra one, there is a next page. No count needed.
- Approximate counts from table statistics or a search engine's estimate; good enough for "about 12,000".
- Cached count with a short TTL or updated incrementally.
- Cap the count: "1,000+" by counting at most 1,000 rows.
- Count once on the first request, reuse for later pages.
Ask whether users really need the exact total. Often "Next" and "Previous" are enough, and infinite scroll never needs a total.
Example
Request page size 20 -> run: SELECT ... LIMIT 21
Got 21 rows -> return 20 and has_next = true
Got 15 rows -> return 15 and has_next = false
No COUNT(*) executed.What to say in the interview
"Exact totals cost a full count on every page. I return has_next by fetching one extra row, or use cached, capped or approximate counts if the UI needs a number. For big datasets I avoid page-number UIs altogether."