Efficient Data Retrieval with MySQL Pagination Using LIMIT

What Is Pagination in MySQL ?

Pagination is the process of dividing a large set of query results into smaller chunks, or “pages.” Instead of returning all rows from a table, you return only a specific portion of the data.

For example:

• Page 1 might show rows 1–10
• Page 2 might show rows 11–20
• Page 3 might show rows 21–30

This approach is widely used in websites and applications because it improves usability and reduces the amount of data transferred in a single query.

Understanding the LIMIT Clause

The LIMIT clause tells MySQL how many rows to return.

Basic Syntax

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows;

Example

SELECT first_name, last_name
FROM employees
LIMIT 5;

Step-by-Step Analysis

• SELECT first_name, last_name chooses the columns to retrieve.
• FROM employees specifies the table.
• LIMIT 5 returns only the first 5 rows from the result set.

This is useful when you only need a small sample of data or want to preview records quickly.

Benefits of Pagination

• It reduces the amount of data returned by each query.
• It makes large result sets easier to browse.
• It improves page load times in web applications.
• It helps avoid overwhelming users with too much information.
• It reduces memory and network usage.

For large tables, pagination can make a major difference in how fast and responsive your application feels.

LIMIT with ORDER BY

When using pagination, sorting is extremely important. Without ORDER BY, MySQL may return rows in an unpredictable order. That means page 1 and page 2 might not always contain consistent results.

Example

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

Step-by-Step Analysis

• ORDER BY salary DESC sorts employees from highest salary to lowest salary.
• LIMIT 5 returns only the top 5 rows after sorting.

This is a common pattern for leaderboards, top-performing employees, or ranked results.

Principle Behind the Logic

MySQL first sorts the rows according to the ORDER BY clause. Then it applies LIMIT to return only the requested number of rows. This sequence is important because pagination should always be based on a stable and predictable ordering.

Using LIMIT and OFFSET for Pagination

To move beyond the first page, you usually need an offset. OFFSET tells MySQL how many rows to skip before starting to return results.

Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
LIMIT number_of_rows OFFSET starting_row;

MySQL also supports an alternative form:

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
LIMIT starting_row, number_of_rows;

Both forms achieve the same result.

Example: Page 1

Suppose we want to display 10 employees per page.

SELECT id, first_name, last_name
FROM employees
ORDER BY id
LIMIT 10 OFFSET 0;

Step-by-Step Analysis

• ORDER BY id sorts rows by employee ID.
• LIMIT 10 asks for 10 rows.
• OFFSET 0 means skip 0 rows, so this returns the first page.

This query returns rows 1 through 10.

Example: Page 2

SELECT id, first_name, last_name
FROM employees
ORDER BY id
LIMIT 10 OFFSET 10;

Step-by-Step Analysis

• ORDER BY id ensures the rows are in a consistent order.
• LIMIT 10 still returns 10 rows.
• OFFSET 10 skips the first 10 rows.

This query returns rows 11 through 20.

Example: Page 3

SELECT id, first_name, last_name
FROM employees
ORDER BY id
LIMIT 10 OFFSET 20;

Step-by-Step Analysis

• The query skips the first 20 rows.
• It returns the next 10 rows after that.
• This represents the third page of results.

Alternative LIMIT Syntax

MySQL also allows this format:

SELECT id, first_name, last_name
FROM employees
ORDER BY id
LIMIT 20, 10;

Step-by-Step Analysis

• 20 is the number of rows to skip.
• 10 is the number of rows to return.

This is equivalent to:

LIMIT 10 OFFSET 20

Both forms are valid, although many developers find the LIMIT ... OFFSET ... form easier to read.

Practical Pagination Example

Imagine a blog application that shows 5 posts per page.

Page 1

SELECT post_id, title, created_at
FROM blog_posts
ORDER BY created_at DESC
LIMIT 5 OFFSET 0;

Page 2

SELECT post_id, title, created_at
FROM blog_posts
ORDER BY created_at DESC
LIMIT 5 OFFSET 5;

Page 3

SELECT post_id, title, created_at
FROM blog_posts
ORDER BY created_at DESC
LIMIT 5 OFFSET 10;

Logic Behind the Query

• ORDER BY created_at DESC shows the newest posts first.
• LIMIT 5 returns 5 posts per page.
• OFFSET determines which set of 5 posts is shown.

This pattern is one of the most common uses of pagination in real-world applications.

Combining WHERE with LIMIT

Pagination is often used together with filtering. For example, you may want to show only active employees and then paginate the results.

SELECT id, first_name, last_name
FROM employees
WHERE department = 'Sales'
ORDER BY id
LIMIT 10 OFFSET 0;

Step-by-Step Analysis

• WHERE department = 'Sales' filters the data first.
• ORDER BY id sorts the filtered rows.
• LIMIT 10 OFFSET 0 returns only the first 10 matching rows.

Principle Behind the Logic

MySQL first applies the WHERE condition, then sorts the remaining rows, and finally applies LIMIT. This order ensures that pagination is based only on the data that matches your filter.

Counting Total Pages

In real applications, pagination often requires knowing how many total pages exist. To do that, you first count the total number of matching rows.

SELECT COUNT(*) AS total_employees
FROM employees
WHERE department = 'Sales';

Step-by-Step Analysis

• COUNT(*) returns the number of rows that match the condition.
• AS total_employees gives the result a readable name.
• This count can be used to calculate the total number of pages.

Example Calculation

If there are 47 matching employees and you show 10 per page:

• Page 1: rows 1–10
• Page 2: rows 11–20
• Page 3: rows 21–30
• Page 4: rows 31–40
• Page 5: rows 41–47

So the total number of pages is 5.

Best Practices for Pagination with LIMIT

Always Use ORDER BY

Without sorting, the rows returned by pagination may appear in an inconsistent order. This can cause duplicate or missing rows across pages.

Keep Page Sizes Reasonable

Large page sizes can slow down the query and make the user experience worse. Common page sizes are 10, 20, or 50 rows.

Use Indexed Columns for Sorting

Sorting on indexed columns can improve performance, especially for large tables.

Avoid Very Large Offsets When Possible

Large offsets can become expensive because MySQL still has to scan and skip many rows. For very large datasets, more advanced techniques such as keyset pagination may perform better.

Test Your Queries

When building pagination logic, always test page 1, a middle page, and the final page to ensure the results are stable and accurate.

Common Mistakes to Avoid

Forgetting ORDER BY

If you paginate without sorting, your pages may not be consistent.

Using Different Filters Between Pages

If the WHERE clause changes from one request to the next, pagination results may become confusing.

Assuming LIMIT Alone Means Pagination

LIMIT restricts rows, but pagination usually also requires OFFSET and a stable ORDER BY clause.

Returning Too Many Rows

Even though MySQL can return large result sets, it is usually better to keep each page small and focused.

A Complete Example

Here is a realistic pagination query for a product catalog:

SELECT product_id, product_name, price
FROM products
WHERE category = 'Books'
ORDER BY product_name ASC
LIMIT 10 OFFSET 20;

Step-by-Step Analysis

• WHERE category = 'Books' selects only book products.
• ORDER BY product_name ASC sorts the books alphabetically.
• LIMIT 10 returns 10 products.
• OFFSET 20 skips the first 20 books, which means this query retrieves the third page of results.