LIMIT and OFFSET for pagination in MySQL - Deep Dive

Overview - LIMIT and OFFSET for pagination

What is it?

LIMIT and OFFSET are commands in SQL used to control how many rows a query returns and where to start returning rows from. They help break large sets of data into smaller, manageable pieces called pages. This is useful when you want to show data in parts, like pages of search results or lists. LIMIT sets the maximum number of rows to return, while OFFSET tells where to start counting rows.

Why it matters

Without LIMIT and OFFSET, queries would return all matching data at once, which can be slow and overwhelming for users and systems. Pagination improves performance and user experience by loading data in chunks. It also reduces memory use and network traffic, making applications faster and more responsive.

Where it fits

Before learning LIMIT and OFFSET, you should understand basic SQL SELECT queries and how data is retrieved from tables. After this, you can learn about advanced pagination techniques, indexing for performance, and user interface design for paginated data.

Mental Model

Core Idea

LIMIT and OFFSET let you slice a big list of data into smaller pages by choosing how many items to show and where to start.

Think of it like...

Imagine a book with many pages. LIMIT is like deciding how many lines you read per page, and OFFSET is like choosing which page to start reading from.

┌───────────────┐
│ Full Data Set │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ OFFSET = 10   │  Skip first 10 rows
│ LIMIT = 5     │  Then take next 5 rows
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Page of Data  │  Rows 11 to 15
└───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Basic SELECT Queries

Concept: Learn how to retrieve data from a table using SELECT.

A SELECT query asks the database to give you rows from a table. For example, SELECT * FROM employees; returns all rows and columns from the employees table.

Result

All rows and columns from the employees table are shown.

Knowing how to get data is the first step before controlling how much data you get.

2

Foundation

Why Limit Data Retrieval Matters

Concept: Understand the need to restrict the number of rows returned.

If a table has thousands of rows, getting all at once can be slow and hard to use. Limiting results helps by showing only a small part at a time.

Result

You realize that fetching all data at once is inefficient and can overwhelm users.

Recognizing the problem of too much data at once sets the stage for learning LIMIT and OFFSET.

3

Intermediate

Using LIMIT to Control Row Count

🤔Before reading on: do you think LIMIT 5 returns the first 5 rows or the last 5 rows? Commit to your answer.

Concept: LIMIT sets the maximum number of rows returned by a query.

Adding LIMIT 5 to a query like SELECT * FROM employees LIMIT 5; returns only the first 5 rows from the result set.

Result

Only 5 rows are shown instead of all rows.

Understanding LIMIT helps you control how much data you get, improving performance and usability.

4

Intermediate

Using OFFSET to Skip Rows

🤔Before reading on: does OFFSET 10 start at row 10 or skip the first 10 rows? Commit to your answer.

Concept: OFFSET tells the database to skip a number of rows before starting to return rows.

Using OFFSET 10 with LIMIT 5 like SELECT * FROM employees LIMIT 5 OFFSET 10; skips the first 10 rows and returns the next 5 rows (rows 11 to 15).

Result

Rows 11 to 15 are shown, skipping the first 10.

Knowing OFFSET lets you jump to any part of the data, enabling page-by-page navigation.

5

Intermediate

Combining LIMIT and OFFSET for Pagination

Concept: Use LIMIT and OFFSET together to fetch specific pages of data.

To show page 3 of a list with 10 items per page, use LIMIT 10 OFFSET 20. This skips 20 rows (pages 1 and 2) and shows the next 10 rows (page 3).

Result

You get exactly the rows for the chosen page, making navigation smooth.

Combining LIMIT and OFFSET creates a simple but powerful way to paginate data.

6

Advanced

Performance Considerations with OFFSET

🤔Before reading on: do you think OFFSET always performs well regardless of the number? Commit to your answer.

Concept: OFFSET can slow down queries on large tables because the database still scans skipped rows.

When OFFSET is large, the database must count and skip many rows before returning results, which can be slow. Alternatives like keyset pagination use indexed columns to improve speed.

Result

You understand that OFFSET is simple but may cause slow queries on big data.

Knowing OFFSET's performance limits helps you choose better pagination methods for large datasets.

7

Expert

Advanced Pagination with Keyset and Seek Methods

🤔Before reading on: do you think LIMIT and OFFSET are the only ways to paginate? Commit to your answer.

Concept: Keyset pagination uses a known column value to fetch the next page, avoiding OFFSET's performance issues.

Instead of OFFSET, keyset pagination uses WHERE conditions like WHERE id > last_seen_id ORDER BY id LIMIT 10. This fetches the next 10 rows after the last seen id efficiently.

Result

Pagination is faster and more scalable on large tables.

Understanding keyset pagination reveals how experts optimize real-world applications beyond basic LIMIT and OFFSET.

Under the Hood

When a query with LIMIT and OFFSET runs, the database engine processes the full result set internally but only returns the specified slice. OFFSET causes the engine to scan and discard rows before the starting point, which can be costly for large offsets. LIMIT simply caps the number of rows sent back to the client.

Why designed this way?

LIMIT and OFFSET were designed as simple, flexible tools to paginate results without changing the query structure. They fit well with SQL's declarative style. However, their simplicity trades off performance for very large datasets, leading to alternative methods like keyset pagination.

┌───────────────┐
│ Query Result  │
│ (All Rows)    │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ OFFSET skips  │
│ first N rows  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ LIMIT returns │
│ next M rows   │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does LIMIT 5 OFFSET 10 return rows 5 to 10 or 11 to 15? Commit to your answer.

Common Belief: LIMIT 5 OFFSET 10 returns rows 5 to 10.

Reality: LIMIT 5 OFFSET 10 skips the first 10 rows and returns rows 11 to 15.

Why it matters: Misunderstanding this causes wrong data pages to be shown, confusing users and breaking pagination.

Quick: Does OFFSET improve query speed by skipping rows faster? Commit to your answer.

Common Belief: OFFSET makes queries faster by skipping rows quickly.

Reality: OFFSET actually makes queries slower on large offsets because the database still processes skipped rows internally.

Why it matters: Assuming OFFSET is always fast leads to slow applications and poor user experience on big data.

Quick: Can you use LIMIT without OFFSET to paginate pages beyond the first? Commit to your answer.

Common Belief: LIMIT alone is enough to paginate any page.

Reality: LIMIT alone only limits row count; OFFSET is needed to skip to later pages.

Why it matters: Ignoring OFFSET means you can only show the first page, limiting navigation.

Quick: Is keyset pagination just a fancy name for LIMIT and OFFSET? Commit to your answer.

Common Belief: Keyset pagination is the same as LIMIT and OFFSET.

Reality: Keyset pagination uses WHERE conditions and indexed columns to avoid OFFSET's performance issues.

Why it matters: Confusing these leads to missed opportunities for optimizing large-scale pagination.

Expert Zone

1

Using OFFSET with large values can cause the database to scan many rows, so it's better to use keyset pagination for deep pages.

2

The order of rows matters: without ORDER BY, LIMIT and OFFSET results can be unpredictable and inconsistent.

3

Combining LIMIT and OFFSET with complex joins or subqueries can affect performance and result accuracy, requiring careful query design.

When NOT to use

Avoid LIMIT and OFFSET for very large datasets or deep pagination because of performance issues. Instead, use keyset pagination or cursor-based methods that rely on indexed columns for faster access.

Production Patterns

In real systems, LIMIT and OFFSET are used for simple pagination on small to medium datasets. For large-scale apps, developers implement keyset pagination with WHERE clauses on unique indexed columns, often combined with caching and API tokens to maintain state.

Connections

Cursor-based Pagination

Builds-on and improves LIMIT/OFFSET by using indexed values instead of row counts.

Knowing LIMIT and OFFSET helps understand why cursor-based pagination is faster and more reliable for large data.

User Interface Design

Pagination controls in UI rely on LIMIT and OFFSET to fetch data pages.

Understanding how LIMIT and OFFSET work helps design better navigation and loading experiences for users.

Memory Paging in Operating Systems

Similar concept of dividing large memory into pages for efficient access.

Seeing pagination as a way to handle large data in chunks connects database queries to how computers manage memory.

Common Pitfalls

#1 Skipping ORDER BY causes inconsistent pagination results.

Wrong approach:

SELECT * FROM employees LIMIT 10 OFFSET 20;

Correct approach:

SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 20;

Root cause:

Without ORDER BY, the database returns rows in an undefined order, so pages can overlap or miss rows.

#2 Using large OFFSET values causes slow queries.

Wrong approach:

SELECT * FROM big_table LIMIT 10 OFFSET 1000000;

Correct approach:

SELECT * FROM big_table WHERE id > last_seen_id ORDER BY id LIMIT 10;

Root cause:

OFFSET makes the database scan and discard many rows, while keyset pagination uses indexed columns to jump directly.

#3 Using LIMIT without OFFSET to paginate beyond first page.

Wrong approach:

SELECT * FROM products LIMIT 10; -- for page 3

Correct approach:

SELECT * FROM products LIMIT 10 OFFSET 20; -- for page 3

Root cause:

LIMIT only limits row count; OFFSET is needed to skip rows for pages after the first.

Key Takeaways

LIMIT and OFFSET are simple SQL tools to paginate data by controlling how many rows to return and where to start.

OFFSET skips rows but can slow down queries on large datasets because the database still processes skipped rows.

Always use ORDER BY with LIMIT and OFFSET to ensure consistent and predictable pagination results.

For large or deep pagination, keyset pagination is a better alternative that uses indexed columns for faster access.

Understanding LIMIT and OFFSET is essential for building efficient, user-friendly data navigation in applications.