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.

Practice

1. What does the LIMIT clause do in a MySQL query?

easy

A. It controls how many rows the query returns.

B. It controls the order of the rows returned.

C. It filters rows based on a condition.

D. It changes the database schema.

Solution

1. Step 1: Understand the purpose of LIMIT

   The LIMIT clause restricts the number of rows returned by a query.

2. Step 2: Compare with other options

   Ordering is done by ORDER BY, filtering by WHERE, and schema changes by DDL commands, not LIMIT.

3. Final Answer:

   It controls how many rows the query returns. -> Option A

4. Quick Check:

   LIMIT = number of rows [OK]

Hint: LIMIT sets max rows returned, like page size [OK]

Common Mistakes:

• Confusing LIMIT with ORDER BY
• Thinking LIMIT filters rows by condition
• Assuming LIMIT changes table structure

2. Which of the following is the correct syntax using the OFFSET keyword to get 10 rows starting from the 7th row in MySQL?

easy

A. SELECT * FROM table LIMIT 6, 10;

B. SELECT * FROM table LIMIT 10 OFFSET 6;

C. SELECT * FROM table OFFSET 6 LIMIT 10;

D. SELECT * FROM table LIMIT 10, 6;

Solution

1. Step 1: Understand LIMIT and OFFSET syntax

   MySQL supports both LIMIT count OFFSET offset and LIMIT offset, count syntax.

2. Step 2: Identify correct syntax for 10 rows starting at 7th row

   OFFSET 6 means skip first 6 rows, then LIMIT 10 means get next 10 rows. SELECT * FROM table LIMIT 10 OFFSET 6; uses this correctly.

3. Final Answer:

   SELECT * FROM table LIMIT 10 OFFSET 6; -> Option B

4. Quick Check:

   LIMIT count OFFSET offset = correct syntax [OK]

Hint: Use LIMIT count OFFSET offset for clarity [OK]

Common Mistakes:

• Swapping LIMIT and OFFSET values
• Using OFFSET before LIMIT incorrectly
• Confusing LIMIT offset,count with LIMIT count OFFSET offset

3. Given the table users with rows numbered by id from 1 to 20, what rows will this query return?
SELECT * FROM users ORDER BY id ASC LIMIT 5 OFFSET 10;

medium

A. Rows with id 1 to 5

B. Rows with id 6 to 10

C. Rows with id 11 to 15

D. Rows with id 16 to 20

Solution

1. Step 1: Understand OFFSET and LIMIT effect on rows

   OFFSET 10 skips first 10 rows (id 1 to 10), LIMIT 5 returns next 5 rows.

2. Step 2: Identify which rows are returned

   After skipping 10 rows, rows with id 11 to 15 are returned.

3. Final Answer:

   Rows with id 11 to 15 -> Option C

4. Quick Check:

   OFFSET 10 + LIMIT 5 = rows 11-15 [OK]

Hint: OFFSET skips rows, LIMIT picks next rows [OK]

Common Mistakes:

• Counting OFFSET as inclusive row
• Mixing up OFFSET and LIMIT order
• Assuming OFFSET starts at 1 instead of 0

4. Identify the error in this query intended to get 5 rows starting from the 3rd row:
SELECT * FROM products LIMIT OFFSET 3, 5;

medium

A. Syntax error: LIMIT should come before OFFSET without comma.

B. OFFSET cannot be used with LIMIT in this way.

C. No error; query is correct.

D. LIMIT and OFFSET are reversed in order.

Solution

1. Step 1: Check correct syntax for LIMIT and OFFSET

   Correct syntax is either LIMIT count OFFSET offset or LIMIT offset, count without the word OFFSET in the second form.

2. Step 2: Identify error in given query

   The query uses both LIMIT and OFFSET keywords together incorrectly with a comma, causing syntax error.

3. Final Answer:

   Syntax error: LIMIT should come before OFFSET without comma. -> Option A

4. Quick Check:

   LIMIT offset,count or LIMIT count OFFSET offset [OK]

Hint: Use either LIMIT offset,count or LIMIT count OFFSET offset [OK]

Common Mistakes:

• Mixing LIMIT and OFFSET keywords with commas
• Using OFFSET before LIMIT
• Adding OFFSET keyword with comma syntax

5. You want to display page 4 of a product list, showing 15 products per page, ordered by created_at descending. Which query correctly uses LIMIT and OFFSET?

hard

A. SELECT * FROM products ORDER BY created_at DESC LIMIT 45 OFFSET 15;

B. SELECT * FROM products ORDER BY created_at DESC LIMIT 60 OFFSET 15;

C. SELECT * FROM products ORDER BY created_at DESC LIMIT 15, 45;

D. SELECT * FROM products ORDER BY created_at DESC LIMIT 15 OFFSET 45;

Solution

1. Step 1: Calculate OFFSET for page 4 with 15 items per page

   Page 1 shows rows 0-14, page 2 rows 15-29, page 3 rows 30-44, so page 4 starts at offset 45.

2. Step 2: Write query with LIMIT and OFFSET

   LIMIT 15 means 15 rows per page, OFFSET 45 skips first 45 rows. SELECT * FROM products ORDER BY created_at DESC LIMIT 15 OFFSET 45; matches this.

3. Final Answer:

   SELECT * FROM products ORDER BY created_at DESC LIMIT 15 OFFSET 45; -> Option D

4. Quick Check:

   OFFSET = (page-1)*page_size = 45 [OK]

Hint: OFFSET = (page-1) * page_size, LIMIT = page_size [OK]

Common Mistakes:

• Swapping LIMIT and OFFSET values
• Calculating OFFSET incorrectly
• Using LIMIT as total rows instead of per page