SQL Help

SQL_CALC_FOUND_ROWS is deprecated: how to paginate with a total count in MySQL 8

Published August 2026 · SQL Help series

What it did, and why everyone used it

Every paginated listing needs two things: this page's rows and the total number of rows, so you can render "Page 3 of 41". The classic MySQL trick got both from one query plus a follow-up call:

SELECT SQL_CALC_FOUND_ROWS id, title
FROM articles
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 20 OFFSET 40;

SELECT FOUND_ROWS();      -- total rows the query WOULD have returned without LIMIT

Elegant on the surface. Underneath, it forced MySQL to compute the entire result set — every matching row, fully sorted — just to count it, then throw all but 20 away. On small tables nobody noticed. On large ones it was routinely slower than running a plain COUNT(*) and the LIMIT query separately, because a bare count can be answered from an index without touching the rows. That, plus the awkward stateful second call, is why it was deprecated.

Replacement 1: two queries (the default)

SELECT COUNT(*) AS total
FROM articles
WHERE status = 'published';

SELECT id, title
FROM articles
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 20 OFFSET 40;

Same WHERE in both — factor it into one place in your code so they can't drift apart. With an index on (status, published_at), the count is answered from the index and the page query seeks straight to its rows. This is what the MySQL team themselves recommend, and for most applications it's the end of the story.

If you're moving old code: the change is mechanical — replace the FOUND_ROWS() call with the count query, drop the modifier. In PHP PDO terms, one extra fetchColumn().

Replacement 2: one query with a window function

Since MySQL 8.0, COUNT(*) OVER () attaches the total to every row of the page:

SELECT id, title,
       COUNT(*) OVER () AS total
FROM articles
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 20 OFFSET 40;

Every returned row carries total = 813 (say); read it off the first one. One round-trip, one WHERE, no drift. Two honest caveats: the server still has to identify the whole matching set to count it (it just avoids the sort-then-discard waste), so it isn't magically cheaper than the two-query version on huge tables — and if the page is empty (offset past the end) you get zero rows and therefore no total, so handle that case.

Replacement 3: don't count at all

Ask what the count is for. Very often it's only to decide whether to show a "Next" button. Then fetch one row more than the page size:

SELECT id, title
FROM articles
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 21 OFFSET 40;      -- page size 20, +1 sentinel

Got 21 back? There's a next page — display 20 and show the button. Got 20 or fewer? You're on the last page. No count query, no scan, and it's what most large sites actually do — the exact "of 41 pages" is quietly dropped, or replaced with a cached approximate ("about 800 results") that's refreshed every few minutes.

While you're here: OFFSET has the same disease

LIMIT 20 OFFSET 40000 makes MySQL walk 40,020 rows and discard 40,000 — page 2000 of anything is slow for exactly the reason SQL_CALC_FOUND_ROWS was. The fix is keyset pagination: remember where the last page ended and seek from there:

-- first page
SELECT id, title, published_at
FROM articles
WHERE status = 'published'
ORDER BY published_at DESC, id DESC
LIMIT 20;

-- next page: pass back the last row's (published_at, id)
SELECT id, title, published_at
FROM articles
WHERE status = 'published'
  AND (published_at, id) < ('2026-08-10 09:14:00', 5123)
ORDER BY published_at DESC, id DESC
LIMIT 20;

Constant time per page, however deep you go, and results don't shift when rows are inserted between clicks. The trade-off: no "jump to page 37" — which, again, users almost never do. Infinite scroll, "load more", and API cursors are all keyset pagination wearing different clothes.

Summary