Introduction

Skipping the first ten rows in MySQL is a standard pagination task, but it is easy to implement it in a way that becomes slow or inconsistent as data grows. The SQL itself is short, yet correct behavior depends on deterministic ordering, safe query construction, and index strategy. A strong pattern here prevents duplicate rows across pages and avoids latency spikes under production load.

Using LIMIT and OFFSET Correctly

MySQL supports two equivalent forms for paging:

1SELECT id, title, created_at
2FROM posts
3ORDER BY created_at DESC, id DESC
4LIMIT 10 OFFSET 10;

1SELECT id, title, created_at
2FROM posts
3ORDER BY created_at DESC, id DESC
4LIMIT 10, 10;

Both queries skip ten rows and return the next ten. The critical part is not the syntax, it is ORDER BY. Without a stable order, page boundaries drift because MySQL can return rows in different physical order between executions.

A robust sort uses a tie-breaker column:

ORDER BY created_at DESC, id DESC

If several rows share the same timestamp, id provides a deterministic secondary key.

Building Pagination in Application Code

Most bugs come from dynamic SQL assembly in the application layer. Keep pagination values parameterized and computed from a clear page formula:

1import mysql.connector
2
3page = 2
4page_size = 10
5offset = (page - 1) * page_size
6
7conn = mysql.connector.connect(
8    host="127.0.0.1",
9    user="app",
10    password="secret",
11    database="blog"
12)
13
14with conn.cursor(dictionary=True) as cur:
15    cur.execute(
16        """
17        SELECT id, title, created_at
18        FROM posts
19        WHERE status = %s
20        ORDER BY created_at DESC, id DESC
21        LIMIT %s OFFSET %s
22        """,
23        ("published", page_size, offset),
24    )
25    rows = cur.fetchall()
26
27print(f"fetched {len(rows)} rows")
28conn.close()

This prevents injection bugs and keeps request behavior predictable. Keep one pagination utility function in your codebase so all endpoints share the same formula and constraints.

Why Deep Offsets Become Slow

OFFSET is simple but not free. MySQL still reads and discards skipped rows before returning the requested window. A request for page 5000 often touches many rows that never reach the client.

For deep scrolling, keyset pagination is usually better:

1SELECT id, title, created_at
2FROM posts
3WHERE status = 'published'
4  AND (created_at, id) < ('2026-03-01 11:22:00', 918273)
5ORDER BY created_at DESC, id DESC
6LIMIT 10;

Here the client sends the last seen key from the previous page, then fetches the next slice. This reduces skipped-row work and gives stable navigation even while new rows are inserted.

Index Design for Fast Page Queries

Pagination performance depends on index alignment with both filtering and sorting. If you query published posts ordered by creation time, a composite index is usually needed:

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

This allows MySQL to satisfy filtering and order with one index traversal. If your query profile differs by endpoint, benchmark each query shape with EXPLAIN instead of guessing.

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

Check for filesort or large row estimates. Those are early warnings that paging cost will grow badly under traffic.

Total Count and Consistency Concerns

Many UIs want both a page slice and a total record count. Usually that means two queries:

SELECT COUNT(*)
FROM posts
WHERE status = 'published';

If data changes between count and page query, totals can appear inconsistent. For dashboard use cases this is acceptable. For financial or audit views, consider transactional reads or cursor-based APIs that avoid page-number semantics.

For background jobs, do not use offset loops for full-table processing. Prefer a monotonic cursor approach:

1SELECT id, payload
2FROM events
3WHERE id > 420000
4ORDER BY id ASC
5LIMIT 1000;

Store the last processed key, then continue. This pattern is easier to resume after failure and scales better.

Common Pitfalls

• Paginating without explicit ORDER BY, which causes unstable page contents.
• Using a non-unique sort key without tie-breaker and seeing duplicates between pages.
• Building SQL with string concatenation instead of parameterized values.
• Using very large offsets in user-facing APIs and accepting avoidable latency.
• Reusing offset pagination for ETL backfills, then struggling with missed or repeated rows.

Summary

• 'LIMIT plus OFFSET is valid for simple paging, but only with deterministic ordering.'
• Include a tie-breaker column such as id in ordered queries.
• Parameterize page inputs in application code for safety and consistency.
• Move to keyset pagination for deep page traversal and better performance.
• Align indexes with both WHERE and ORDER BY clauses to keep queries fast.