Imagine loading an online store with 50,000 items. Fetching all 50,000 products over the network in a single query would overwhelm the browser, waste memory, and slow down your application. To solve this, web applications break large datasets into manageable chunks—a process known as pagination.

In MySQL, pagination relies on two critical mechanisms: LIMIT (which controls how many rows to return) and OFFSET (which controls how many rows to skip). However, pagination only works reliably when paired with explicit sorting via ORDER BY. Without deterministic row ordering, your paginated results can skip rows or present duplicate items across pages.

1. Try This First

Before diving into pagination formulas, run these two basic queries against a sample table to see how row ordering behaves.

First, execute a plain SELECT without an explicit order:

SELECT product_name, price
FROM products;

Next, add an explicit ORDER BY clause:

SELECT product_name, price
FROM products
ORDER BY price ASC;

What to compare: In the first query, MySQL returns rows in whatever physical sequence it retrieved them during table scanning. In the second query, MySQL guarantees that the cheapest products appear at the top, sorted strictly by price in ascending order.

2. Row Order Without ORDER BY

When a SELECT query does not contain an ORDER BY clause, you should never assume rows will return in the order they were inserted or by primary key.

Under the hood, the relational database engine selects an execution plan based on available indexes, cached memory, and table statistics. If MySQL decides to perform a parallel scan, read from an index, or merge join operations, the resulting row sequence can change unpredictably.

If output sequence matters to your user interface, report generation, or paginated API response, you must make that requirement explicit by writing an ORDER BY clause.

3. Ascending and Descending Sort

SQL provides two directional keywords for ordering:

• ASC: Ascending order (lowest to highest, A to Z, oldest to newest). This is the default.
• DESC: Descending order (highest to lowest, Z to A, newest to oldest).

To display products from highest price to lowest price, use DESC:

SELECT product_name, price
FROM products
ORDER BY price DESC;

Although ASC is implicit when no direction is specified, writing ASC explicitly in production code makes your code intent clearer to other developers.

4. Multiple-Column Sorting

You can sort query results by multiple expressions separated by commas. MySQL evaluates these sorting expressions sequentially from left to right.

SELECT product_name, category, price
FROM products
ORDER BY category ASC, price DESC;

Here is how MySQL processes this multi-column sort:

• category ASC is the primary sorting expression. All rows are grouped alphabetically by category.
• price DESC is the secondary sorting expression. It is evaluated only when category values in adjacent rows are identical.

5. Deterministic Tie-Breaking

Understanding tie-breaking is critical when implementing pagination with LIMIT and OFFSET.

Consider this query:

SELECT product_name, price
FROM products
ORDER BY price DESC;

If five products share the exact price of $99.99, MySQL has no rule for ordering those five tied items. If Page 1 ends in the middle of those tied items and Page 2 requests the next set of rows, tied rows might shift across pages, causing users to see duplicate items or miss items entirely.

To guarantee deterministic ordering across paginated requests, add a unique column (such as the primary key id) as a tie-breaker:

SELECT product_name, price, id
FROM products
ORDER BY price DESC, id ASC;

6. Paginating with LIMIT & OFFSET

In MySQL, LIMIT specifies the maximum number of rows to return, while OFFSET specifies how many rows to skip before returning data.

To calculate OFFSET in application code for a specific page number, use this formula:

OFFSET = (page_number - 1) * page_size

Page 1 (First 5 Products)

SELECT product_name, price
FROM products
ORDER BY price DESC, id ASC
LIMIT 5 OFFSET 0;

Page 2 (Next 5 Products)

SELECT product_name, price
FROM products
ORDER BY price DESC, id ASC
LIMIT 5 OFFSET 5;

Combining Filtering with Pagination

When combining filtering (WHERE), sorting (ORDER BY), and pagination (LIMIT / OFFSET), SQL requires a specific clause sequence:

SELECT product_name, price
FROM products
WHERE category = 'Laptop'
ORDER BY price DESC, id ASC
LIMIT 5 OFFSET 0;

In this execution flow, WHERE filters the table rows first, ORDER BY sorts the matching rows, and LIMIT / OFFSET extracts the requested page window.

7. NULL Values Ordering

How missing or NULL data is ordered depends on the database engine:

• In MySQL: NULL values are treated as lower than any non-NULL value. Thus, in ascending sort (ASC), NULL values appear first. In descending sort (DESC), NULL values appear last.
• In PostgreSQL: PostgreSQL provides explicit control using NULLS FIRST and NULLS LAST.

Here is how you explicitly control NULL placement in PostgreSQL:

-- PostgreSQL Syntax Example
SELECT product_name, price
FROM products
ORDER BY price ASC NULLS LAST;

If you need to force NULL values to the end in MySQL, you can sort using a conditional expression such as ORDER BY (price IS NULL) ASC, price ASC.

8. Inspecting Queries with EXPLAIN

You can inspect how MySQL executes your sorting and pagination queries by prepending EXPLAIN to your query:

EXPLAIN
SELECT product_name, price
FROM products
ORDER BY price DESC;

Depending on the query, indexes, filters, and table volume, MySQL may need an additional sorting phase. EXPLAIN helps you inspect what the database optimizer actually chose.

If the Extra column in MySQL displays Using filesort, it indicates that MySQL could not fulfill the requested sort order directly from an index and had to perform an extra sorting step in memory or on disk.

9. My Practical Rules for ORDER BY & Pagination

• Write explicit ORDER BY clauses: If row order matters to your application, always write ORDER BY. Never rely on default insertion sequence.
• Include a unique tie-breaker: Add a unique column (such as primary key id) to your sort expression for stable, leak-free pagination.
• Be explicit with ASC / DESC: Write sort directions clearly to avoid ambiguity.
• Inspect execution plans before indexing: Run EXPLAIN to see if your query triggers expensive sorting passes before creating new indexes.
• Remember index overhead: Indexes take disk space and slow down INSERT, UPDATE, and DELETE statements. Avoid adding indexes solely because an ORDER BY clause exists.

10. Step-by-Step Practical Setup

Follow these practical steps in your MySQL environment (such as MySQL Workbench, phpMyAdmin, or MySQL CLI) to build and paginate a standard products table.

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(50) NOT NULL,
  price DECIMAL(10, 2) NULL
);

INSERT INTO products (product_name, category, price) VALUES
('Pro Laptop', 'Electronics', 1200.00),
('Wireless Mouse', 'Electronics', 25.00),
('Mechanical Keyboard', 'Electronics', 85.00),
('HD Monitor', 'Electronics', 220.00),
('USB-C Cable', 'Electronics', 12.50),
('Desk Chair', 'Furniture', 150.00),
('Standing Desk', 'Furniture', 450.00),
('Desk Lamp', 'Furniture', 25.00),
('Prototype Item', 'Electronics', NULL);

SELECT id, product_name, price
FROM products
ORDER BY price DESC, id ASC
LIMIT 3 OFFSET 0;

SELECT id, product_name, price
FROM products
ORDER BY price DESC, id ASC
LIMIT 3 OFFSET 3;

SELECT id, product_name, price
FROM products
ORDER BY price DESC, id ASC
LIMIT 3 OFFSET 6;

11. Common Pagination Problems & Fixes

12. Homework Exercises

13. Live SQL Playground

Test pagination and sorting directly in your browser using SQLite WebAssembly sandbox.

14. Module 13 Quiz

Test your knowledge of SQL LIMIT, OFFSET, and ORDER BY sorting mechanics.

15. Frequently Asked Questions

16. Official References

• MySQL 8.0 Reference Manual: ORDER BY Optimization
• MySQL 8.0 Reference Manual: LIMIT Query Optimization
• MySQL 8.0 Reference Manual: How MySQL Uses Indexes
• PostgreSQL 17 Documentation: Sorting Rows (ORDER BY)

Related SQL Lessons

• Module 1: The SELECT Statement Essentials
• Setting Up Local MySQL Environment with XAMPP
• How to Install MySQL Step by Step

Part of the CodeMend SQL Learning Module Series.