LIMIT & OFFSET

Introduction

LIMIT caps the number of returned rows. OFFSET skips rows. Together they are used for pagination — but for large datasets, OFFSET pagination becomes slow and you should switch to keyset pagination.

Understanding the topic

Core concepts to understand:

• SELECT ... LIMIT 20 OFFSET 0; — page 1.
• ... LIMIT 20 OFFSET 40; — page 3.
• OFFSET is O(offset) — page 1000 reads 20,000 rows.
• Keyset pagination: WHERE id < last_seen_id ORDER BY id DESC LIMIT 20 — O(1).
• MySQL & Postgres support LIMIT; SQL Server uses OFFSET ... FETCH NEXT.

Syntax reference

Visual workflow / architecture:

OFFSET pagination (slow at depth)
page 1   page 2   page 3   ...   page 1000
[1-20]  [21-40]  [41-60]   ...   [skip 19,980 rows!]

Keyset pagination (fast at any depth)
WHERE id < last_id ORDER BY id DESC LIMIT 20
─── only reads 20 rows, always.

SELECT <cols>
FROM   <table>
ORDER  BY <col>
LIMIT  <count>
OFFSET <skip>;

SELECT id, title
FROM   posts
ORDER  BY id DESC
LIMIT  2 OFFSET 2;   -- page 2 (size 2)

Real-world use

Twitter, Instagram and Stripe Dashboard all use keyset pagination because OFFSET breaks beyond the first 100 pages. Use cursors or last-id pointers in your APIs.

Best practices

• Use OFFSET for shallow pagination only (first 100 pages).
• Switch to keyset pagination for infinite scroll / deep pages.
• Always have a stable ORDER BY when paginating.

Common mistakes

• OFFSET on a 10M row table page 5000 — multi-second query.
• Pagination without ORDER BY → unstable, duplicate rows.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. What is keyset pagination?
• Q2. Why is OFFSET slow at depth?
• Q3. SQL Server equivalent of LIMIT.
• Q4. Why must paginated queries have ORDER BY?
• Q5. Use case for OFFSET pagination.