Introduction

Yes, you can paginate in MySQL without doing a separate COUNT(*) query, but the exact technique depends on what the UI needs. If you only need the next page and a has_next flag, the solution is simple. If you need an exact total page count, one query can still do it in some cases, but it often does not remove the core cost of counting.

Decide What Pagination Information You Actually Need

There are two common pagination requirements:

• Fetch one page and know whether another page exists.
• Fetch one page and also know the exact total number of rows.

Those are different problems. The first can be solved cheaply without a second query. The second usually requires some form of counting, even if you squeeze it into one SQL statement.

Pattern 1: Fetch page_size + 1

If your page size is 20, fetch 21 rows. Return the first 20 to the client and use row 21 only to decide whether there is another page.

1SELECT id, created_at, title
2FROM posts
3WHERE status = 'published'
4ORDER BY created_at DESC, id DESC
5LIMIT 21;

Application logic:

• Return rows 1 through 20.
• If row 21 exists, set has_next = true.

This avoids a separate count query and is usually enough for infinite scroll or simple next-page UIs.

Pattern 2: Keyset Pagination for Better Scale

Offset-based pagination gets slower as offsets grow because MySQL still has to scan and skip rows. Keyset pagination uses the last seen row as a cursor.

1SELECT id, created_at, title
2FROM posts
3WHERE status = 'published'
4  AND (created_at, id) < ('2026-03-07 12:00:00', 4812)
5ORDER BY created_at DESC, id DESC
6LIMIT 20;

This is usually better when:

• The table is large.
• Users page deeply.
• You care more about next-page speed than page-number navigation.

Keyset pagination is often the real performance answer, not just "one fewer query".

Pattern 3: Exact Total with a Window Function

If you truly need total rows in one SQL statement and you are on MySQL 8, a window function can return it:

1SELECT id, created_at, title, total_rows
2FROM (
3    SELECT
4        id,
5        created_at,
6        title,
7        COUNT(*) OVER() AS total_rows
8    FROM posts
9    WHERE status = 'published'
10    ORDER BY created_at DESC, id DESC
11) AS ranked
12LIMIT 20 OFFSET 40;

This avoids a second round trip, but it does not make counting free. MySQL still has to compute the total over the matching rows.

That is why "one query" and "fast" are not the same thing.

Why Large OFFSET Is Expensive

A query like this:

1SELECT id, created_at, title
2FROM posts
3WHERE status = 'published'
4ORDER BY created_at DESC, id DESC
5LIMIT 20 OFFSET 100000;

looks simple, but MySQL still processes and discards many earlier rows before returning the page you asked for. That cost grows with page depth.

If users really navigate to page 5000, keyset pagination usually wins.

Indexing Matters More Than Query Count

No pagination pattern performs well without the right index. For the examples above, an index aligned with filter and sort order is critical.

Example:

CREATE INDEX idx_posts_status_created_id
ON posts (status, created_at DESC, id DESC);

The exact index design depends on your filters and ordering, but pagination performance is usually dominated by indexing, not by whether you issue one SQL statement or two.

What If the UI Requires Total Pages

If the UI shows exact page numbers and total pages, you need an exact count from somewhere:

• A live COUNT(*).
• A window-function count.
• A cached or precomputed total.

There is no magical pagination query that returns an exact total and a cheap deep page without doing the underlying work.

If the UI can accept has_next instead of exact totals, the system becomes simpler and often much faster.

Practical Recommendation

Use these rules:

• Use LIMIT page_size + 1 when you only need next-page information.
• Use keyset pagination for large datasets and deep browsing.
• Use exact counts only when the product genuinely needs them.

That framing produces better systems than chasing the smallest possible number of SQL statements.

Common Pitfalls

• Assuming "one query" automatically means better performance.
• Using large OFFSET values on big tables without measuring cost.
• Forcing exact total-page counts into UIs that only need next-page navigation.
• Ignoring index design while focusing only on SQL shape.
• Using keyset pagination without a stable deterministic sort order.

Summary

• You can paginate without double-querying if you only need page data and has_next.
• 'LIMIT page_size + 1 is the simplest pattern for that case.'
• Keyset pagination is often the best scalable solution.
• Exact totals still require counting work, even when done in one statement.
• Index design matters more than shaving one query off the request path.