Efficient Data Retrieval with MySQL Pagination Using LIMIT

I remember first encountering a performance issue in a project where we needed to display hundreds of thousands of employee records in a web application. Retrieving all the data at once was a nightmare—pages loaded extremely slowly, and sometimes the application would even crash. It was then that I truly understood how MySQL pagination works and why correctly using LIMIT can save time and avoid a lot of trouble.

Pagination in MySQL

In that project, we needed to display employee information in batches—otherwise, the interface would be completely unusable. Pagination essentially divides the query results into smaller “pages.” This way, you don’t need to retrieve all the information; you only need the content required for the current page.

For instance:

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

Initially, I thought that using LIMIT alone would be sufficient, but I soon discovered that without a consistent sorting method, the “first page” might display different rows each time it was refreshed. This led me to start researching the combination of ORDER BY and LIMIT.

Understanding the LIMIT Clause

When I started debugging, I ran a simple query to quickly check a few employees:

SELECT first_name, last_name
FROM employees
LIMIT 5;

Initially, I thought it would return the first 5 employees in alphabetical order—but MySQL only returned the first 5 rows of data stored in the table. This was my first lesson: the LIMIT statement itself does not guarantee the order; it only limits the size of the result set.

Step by step, here’s what’s happening:

1. SELECT first_name, last_name → choose columns.
2. FROM employees → specify the table.
3. LIMIT 5 → return the first 5 rows MySQL encounters.

Useful for previews or quick testing, but for actual pagination, you need sorting.

Why ORDER BY Matters

When we tried to display the highest salary, the results were inconsistent. Fortunately, a simple query solved the problem:

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

Here, ORDER BY salary DESC ensures the highest-paid employees always appear on top. Without it, the first page might randomly show different employees every time. The principle is clear: MySQL sorts first, then applies LIMIT. Always remember that sequence—it avoids a lot of subtle bugs.

Using LIMIT with OFFSET

Once the first page was working, we needed page 2, page 3… Here’s where OFFSET comes in. In our employee directory, showing 10 rows per page, the queries looked like this:

Page 1:

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

Page 2:

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

Page 3:

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

Initially, I tried using LIMIT 10, 10 for the second page. It works fine, but when reading the code afterwards, I keep getting the offset and line number mixed up. After a lot of trouble debugging our colleagues’ code, we finally agreed that LIMIT ... OFFSET ... was more readable.

A Real-Life Blog Pagination Example

I also worked on a blog app where we had hundreds of posts. Displaying the newest posts first is critical, so this query became our go-to:

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;

Here, ORDER BY created_at DESC made debugging easier. When users complained about missing posts, I realized it was due to inconsistent ordering on a query I initially wrote without ORDER BY. Lesson learned: always sort before paginating.

Combining WHERE with LIMIT

In another project, we only wanted to display active sales staff. Our initial approach was sloppy—inconsistent page content resulted from incorrectly applied filters. The solution is as follows:

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

My key takeaway: MySQL applies WHERE first, then ORDER BY, and finally LIMIT. This order matters because you want your pages to reflect only filtered data.

Counting Total Pages

Users often ask, “How many pages are there?” I learned the hard way that guessing doesn’t work. First, we count:

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

For example: 47 sales staff, 10 per page → 5 pages in total. I have actually run this and carefully checked it, because an error in calculating the page count would result in the last page being empty, thus causing user dissatisfaction.

Based on experience from multiple projects, I now adhere to the following principles:

1. Always use the ORDER BY clause – Otherwise, page order may be disrupted.
2. Maintain a reasonable page size – 10, 20, or 50 rows are generally suitable.
3. Sort the indexed columns – This can significantly improve performance.
4. Avoid using excessively large offsets – For massive datasets, OFFSET may be slow; key-set pagination may be more appropriate.
5. Thoroughly test pages – Page 1, middle pages, and the last page are all essential.

Common Mistakes I Encountered:

• Forgetting to use the ORDER BY statement → Row order becomes disordered.
• Using inconsistent filter criteria → Leading to missing or duplicate rows.
• Mistakenly believing that using only the LIMIT statement will achieve pagination → Results are not as expected.

* Returning excessively large pages → Application crashes.

In one product catalog, we needed to show books in a category:

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

This returned the third page of books in alphabetical order. I remember initially omitting the “ORDER BY” parameter, resulting in completely different results with each refresh—the user was confused, and it took me a full hour to debug and find the problem.