Chapter 6: Sorting & Pagination with ORDER BY & LIMIT

Welcome to Chapter 6. A fundamental principle of relational database theory is that tables represent unordered sets of rows. Unless you explicitly add an ORDER BY clause, MySQL does not guarantee any specific row order. Furthermore, returning thousands of rows at once degrades network throughput and browser rendering speed.

In this chapter, we will master single and multi-column sorting, constrain query volume with LIMIT, and implement production pagination strategies.

🖈 6.1 Sorting with ORDER BY (ASC vs DESC)

The ORDER BY clause sorts query output. By default, sorting is performed in ascending order (ASC). Use DESC to sort in descending order:

-- Sort employees from highest salary to lowest
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

Multi-Column Sorting (Tie Breaking)

When multiple records share identical values in the primary sorting column, you specify secondary and tertiary columns to break ties deterministically:

-- Sort primarily by department ascending; break ties by salary descending
SELECT department_id, first_name, last_name, salary
FROM employees
ORDER BY department_id ASC, salary DESC;

📝 6.2 Constraining Result Volume with LIMIT

The LIMIT clause restricts the maximum number of rows returned by the query:

-- Select the 5 highest earning employees
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

-- Select the 5 least earning employees
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary ASC
LIMIT 5;

📄 6.3 Pagination Syntax (OFFSET)

In web applications, datasets are displayed across pages (e.g. 10 records per page). MySQL supports offset pagination using LIMIT offset, count or LIMIT count OFFSET offset:

-- Formula: OFFSET = (PageNumber - 1) * ItemsPerPage

-- Page 1: First 10 records (skip 0, take 10)
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY employee_id ASC
LIMIT 0, 10;

-- Page 2: Next 10 records (skip 10, take 10)
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY employee_id ASC
LIMIT 10, 10;

-- Page 3: Records 21-30 (skip 20, take 10)
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY employee_id ASC
LIMIT 20, 10;

⚠ 6.4 The “Deep Paging” Problem & Keyset Cursor Pagination

⚠ Senior Developer Alert: If you write SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;, MySQL must fetch, sort, and discard 1,000,000 rows just to return the 10 rows you requested! This causes severe database lag on large tables.

The High-Performance Solution: Keyset (Cursor) Pagination

Instead of skipping rows with OFFSET, remember the last seen ID from the previous page and filter with WHERE id > last_seen_id:

-- High-performance cursor pagination:
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE employee_id > 120
ORDER BY employee_id ASC
LIMIT 10;

Because employee_id is indexed, MySQL jumps directly to ID 121 via B-Tree index seek in O(log N) time, completely bypassing millions of preceding rows!

❓ Frequently Asked Questions (FAQ)

1. Does LIMIT execute before or after ORDER BY?

In the SQL execution lifecycle, ORDER BY executes before LIMIT. MySQL sorts the matching result set first, and then slices off the requested number of rows specified by LIMIT.

2. Can I use column position numbers in ORDER BY?

Yes, e.g. ORDER BY 2 DESC sorts by the second column in the SELECT list. However, this is considered an anti-pattern in production code because altering the column order in your SELECT statement unintentionally changes your sorting logic.