LIMIT caps the number of rows returned; OFFSET skips rows first. Together they page through results — but deep offsets get slow.

Top-N

SQL

SELECT name, salary FROM employees
ORDER BY salary DESC, emp_id
LIMIT 3;

Output

name  | salary
------+-------
Asha  | 150000
Ravi  | 120000
Priya | 110000

Offset pagination

SQL

-- page 2, page size 3 → OFFSET = (page - 1) * size
SELECT emp_id, name FROM employees
ORDER BY emp_id
LIMIT 3 OFFSET 3;

Output

emp_id | name
-------+------
4      | Karan
5      | Priya
6      | Arjun

Engine	Page 2 of size 3
SQLite, PostgreSQL, MySQL	`LIMIT 3 OFFSET 3` (MySQL also `LIMIT 3, 3` — offset first!)
SQL Server 2012+	`ORDER BY emp_id OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY`
Oracle 12c+ / standard	`OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY`
Old Oracle	`WHERE ROWNUM <= n` in a nested query

Keyset (seek) pagination

OFFSET 100000 still reads and throws away 100 000 rows, and rows inserted between requests shift pages (duplicates / gaps). Keyset pagination remembers the last key seen and seeks past it using the index.

SQL

-- the client got page 1 ending at emp_id = 3; next page:
SELECT emp_id, name FROM employees
WHERE emp_id > 3
ORDER BY emp_id
LIMIT 3;

Output

emp_id | name
-------+------
4      | Karan
5      | Priya
6      | Arjun

SQL

-- sorted by a non-unique column: seek on (salary, emp_id) together; last row seen was Meera (95000, 3)
SELECT emp_id, name, salary FROM employees
WHERE (salary, emp_id) < (95000, 3)
ORDER BY salary DESC, emp_id DESC
LIMIT 3;

Output

emp_id | name   | salary
-------+--------+-------
9      | Sara   | 85000
8      | Vikram | 80000
7      | Neha   | 72000

Why is OFFSET pagination slow for large page numbers?