✏️ Explanatory Question
Level: Intermediate — Crucial for building paginated APIs and controlling large result sets.
The LIMIT clause restricts the number of rows returned by a query. It is one of the most useful clauses for performance, previews, and building pagination ("page 1, page 2...") in applications.
LIMIT accepts one or two arguments:
LIMIT count: Returns the first count rows.LIMIT offset, count: Skips offset rows, then returns count rows.LIMIT 0, 10 returns rows 1–10, and LIMIT 10, 10 returns rows 11–20. You can also write it as LIMIT 10 OFFSET 10.
To fetch a specific page, calculate the offset from the page number and page size:
$$ offset = (page\_number - 1) \times page\_size $$
| Page | Offset | LIMIT Clause | Rows Returned |
|---|---|---|---|
| 1 | 0 | LIMIT 0, 10 | 1–10 |
| 2 | 10 | LIMIT 10, 10 | 11–20 |
| 3 | 20 | LIMIT 20, 10 | 21–30 |
-- Return only the first 5 rows
SELECT * FROM employees LIMIT 5;
-- Skip 10 rows, then return 10 (page 2)
SELECT * FROM employees LIMIT 10, 10;
-- Same using OFFSET syntax
SELECT * FROM employees LIMIT 10 OFFSET 10;
-- Pagination with sorting (always sort for consistent pages)
SELECT id, name FROM employees
ORDER BY id
LIMIT 20, 10; -- page 3
-- Top 3 highest-paid employees
SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 3;LIMIT 1000000, 10) are slow because MySQL still scans all skipped rows. For deep pagination, use keyset pagination (e.g., WHERE id > last_seen_id ORDER BY id LIMIT 10) instead.