LIMIT and OFFSET in MySQL: Pagination Explained with Examples

Returning ten million rows to a web page that only shows twenty at a time would be both wasteful and unusable. LIMIT and OFFSET are MySQL's tools for returning just a manageable slice of a result set — the foundation of pagination in virtually every application with a 'page 1, 2, 3...' style interface.

What Are LIMIT and OFFSET?

LIMIT restricts a query's result set to a specified maximum number of rows. OFFSET specifies how many rows to skip from the beginning of the (already sorted) result set before starting to return rows. Used together, they let a query return any specific 'page' of results from a larger set.

What You'll Learn

• Use LIMIT to cap the number of rows returned by a query.
• Use OFFSET alongside LIMIT to implement page-based pagination.
• Understand why ORDER BY is essential for reliable pagination.
• Recognize the performance cost of very large OFFSET values.

Key Terms to Know

• LIMIT: A clause that caps the maximum number of rows a query returns.
• OFFSET: A clause that skips a specified number of rows before returning results.
• Pagination: The practice of dividing a large result set into smaller, sequentially navigable pages.
• Keyset pagination: An alternative pagination technique using a WHERE condition on the last seen value instead of OFFSET, more efficient for very large datasets.

LIMIT: Capping the Number of Rows Returned

SELECT product_name FROM products ORDER BY price DESC LIMIT 5; returns only the 5 most expensive products. LIMIT alone is useful any time you need just the 'top N' or 'first N' rows of an already-sorted result, such as a leaderboard's top 10 or a dashboard's 5 most recent orders.

OFFSET: Skipping Rows for Pagination

SELECT product_name FROM products ORDER BY product_id LIMIT 10 OFFSET 20; skips the first 20 rows, then returns the next 10 — effectively 'page 3' if each page shows 10 items (since pages 1 and 2 already covered rows 1–20). MySQL also supports the shorthand LIMIT 20, 10, which is equivalent to LIMIT 10 OFFSET 20, though the explicit OFFSET keyword form is generally clearer to read.

In practice, an application computes OFFSET as (page_number - 1) * page_size, turning a user-facing page number into the correct number of rows to skip.

Why ORDER BY Is Essential, and the OFFSET Performance Trap

Without ORDER BY, the 'next' page returned by LIMIT/OFFSET isn't guaranteed to be consistent or non-overlapping with the previous page, since row order isn't guaranteed at all without explicit sorting — pagination without ORDER BY is fundamentally unreliable.

A real performance trap with large OFFSET values: LIMIT 10 OFFSET 100000; still requires MySQL to internally process and discard the first 100,000 rows before returning the next 10, making deep pagination on large tables increasingly slow the further a user pages in. For very large datasets needing deep pagination, keyset pagination (filtering with WHERE id > last_seen_id LIMIT 10, for example) avoids this cost entirely by jumping directly to the right starting point using an index.

Visual Summary

Picture a long line of numbered tickets at a service counter. LIMIT decides how many tickets get called at once, like calling 10 numbers at a time. OFFSET is the instruction to skip past a certain number of tickets before starting to call — skip the first 20, then call the next 10. The catch is that the clerk still has to physically walk past those first 20 tickets to get there, which is exactly why large OFFSET values get slower the further in you go.

LIMIT/OFFSET Pagination Examples

Page	page_size = 10	LIMIT / OFFSET
Page 1	Rows 1–10	LIMIT 10 OFFSET 0
Page 2	Rows 11–20	LIMIT 10 OFFSET 10
Page 3	Rows 21–30	LIMIT 10 OFFSET 20
Page N	Formula	OFFSET = (N - 1) * page_size

SQL Example

-- Top 5 most expensive products
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 5;

-- Page 1 of a paginated product list (10 per page)
SELECT product_id, product_name
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 0;

-- Page 3 of the same paginated list
SELECT product_id, product_name
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 20;

-- Keyset pagination alternative, more efficient for deep pages on large tables
SELECT product_id, product_name
FROM products
WHERE product_id > 1045  -- the last product_id seen on the previous page
ORDER BY product_id
LIMIT 10;

The first query demonstrates LIMIT alone for a simple top-N result. The second and third show classic OFFSET-based pagination for pages 1 and 3 of a 10-item-per-page list. The final query demonstrates the keyset pagination alternative, which uses a WHERE condition referencing the last seen ID instead of OFFSET, allowing MySQL to jump directly to the right starting point via an index rather than scanning past all preceding rows.

Real-World Examples

• E-commerce product listing pages use LIMIT/OFFSET (or increasingly keyset pagination) to display a manageable number of products per page.
• Social media feeds often use keyset-style pagination (sometimes called 'cursor-based pagination') specifically to avoid OFFSET's performance penalty at scale.
• Admin dashboards use simple LIMIT/OFFSET pagination for internal tools where dataset sizes are modest and deep-paging performance is less critical.
• API endpoints commonly expose page and page_size parameters that translate directly into LIMIT and OFFSET values server-side.

Best Practices and Pro Tips

• Always pair LIMIT/OFFSET with an ORDER BY clause that includes a unique column (like a primary key) to guarantee stable, non-overlapping pages across requests.
• For user-facing features with potentially very deep pagination (page 500+) on large tables, seriously consider keyset pagination instead of OFFSET to avoid the increasing performance cost.
• Use LIMIT alone (without OFFSET) for simple 'top N' queries — there's no need to introduce OFFSET when you only ever need the first page.

Common Mistakes to Avoid

• Using LIMIT/OFFSET without an ORDER BY clause, leading to inconsistent or duplicate results across pages since row order isn't otherwise guaranteed.
• Not accounting for the performance cost of very large OFFSET values on big tables, leading to slow page loads deep into pagination.
• Forgetting that OFFSET counts rows from the start of the already-sorted result, not from any particular ID or value.
• Mixing up the order of arguments in the shorthand LIMIT offset, count syntax, which is easy to misread compared to the more explicit LIMIT count OFFSET offset form.

Interview Questions

Q1. What is the difference between LIMIT and OFFSET?

LIMIT specifies the maximum number of rows to return. OFFSET specifies how many rows to skip from the start of the sorted result set before beginning to return rows. They're typically combined to implement page-based pagination.

Q2. Why is ORDER BY considered essential when using LIMIT and OFFSET for pagination?

Without ORDER BY, MySQL doesn't guarantee any particular row order, so consecutive pages fetched with different OFFSET values could overlap, skip rows, or return inconsistent results across requests.

Q3. Why does a very large OFFSET value cause performance problems?

MySQL still has to internally process and discard all the rows up to the offset before it can return the requested rows, meaning the cost of the query grows as the offset increases, even though the same small number of rows is ultimately returned.

Q4. What is keyset pagination, and why is it sometimes preferred over OFFSET?

Keyset pagination uses a WHERE condition referencing the last seen value (such as the last row's ID) instead of OFFSET, allowing MySQL to jump directly to the correct starting point using an index, avoiding the performance cost of scanning past all preceding rows.

Practice MCQs

1. What does LIMIT 5 do in a SELECT query?

1. Returns the 5 most recent rows regardless of order
2. Caps the result set to a maximum of 5 rows
3. Skips the first 5 rows
4. Groups results into sets of 5

Answer: B. Caps the result set to a maximum of 5 rows

Explanation: LIMIT restricts the maximum number of rows returned by the query, applied after any sorting from ORDER BY.

2. What does LIMIT 10 OFFSET 20 return?

1. The first 10 rows
2. Rows 21 through 30 of the sorted result
3. Rows 1 through 20
4. The last 10 rows

Answer: B. Rows 21 through 30 of the sorted result

Explanation: OFFSET 20 skips the first 20 rows, and LIMIT 10 then returns the next 10 rows after that point.

3. Why is pagination unreliable without an ORDER BY clause?

1. LIMIT doesn't work without ORDER BY
2. Row order isn't guaranteed, so pages could overlap or skip rows inconsistently
3. OFFSET requires ORDER BY to function at all
4. MySQL throws an error

Answer: B. Row order isn't guaranteed, so pages could overlap or skip rows inconsistently

Explanation: Without explicit sorting, the underlying row order can vary between query executions, making consistent pagination impossible to guarantee.

Quick Revision Points

• LIMIT caps the number of rows returned; OFFSET skips a number of rows before returning results.
• ORDER BY should always accompany LIMIT/OFFSET for reliable, consistent pagination.
• Large OFFSET values become increasingly slow since MySQL must process and discard skipped rows.
• Keyset pagination (WHERE id > last_seen_id) avoids the large-OFFSET performance trap.

Conclusion

• LIMIT and OFFSET are the foundational tools behind virtually all page-based navigation in applications.
• ORDER BY isn't optional for reliable pagination — it's a hard requirement.
• Knowing when to graduate to keyset pagination is an important practical skill at scale.

LIMIT and OFFSET work together to slice a large, sorted result set into manageable pages, forming the backbone of pagination across countless real-world applications. ORDER BY is non-negotiable for reliable, consistent paging, and very large OFFSET values come with a real performance cost worth understanding before it becomes a production problem. With SELECT, filtering, sorting, and now pagination covered, the next lessons turn to modifying existing data with UPDATE and DELETE.