LIMIT & OFFSET

A shopping site never shows all of its products on one page. It shows the top 10 best sellers, or 20 search results at a time with "Next" and "Previous" buttons. Behind those pages are two small keywords: LIMIT and OFFSET.

In this lesson you will return only the first few rows of a result, skip rows to build page-by-page navigation (called pagination), and learn a faster technique for very large tables.

LIMIT: return only the first rows

LIMIT n goes at the very end of a query and keeps at most n rows of the result. It is almost always used with ORDER BY, so that "first" has a clear meaning.

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

product_name                | price
----------------------------+------
27-inch Monitor             | 15999
Noise-Cancelling Headphones | 8999
1TB External SSD            | 6499
(3 rows)

These are the three most expensive products. MySQL sorts all the rows first, then keeps the first three. That order matters: LIMIT is applied after WHERE and ORDER BY.

Here is another common "top N" question: the three most recent delivered orders.

SELECT order_id, order_date
FROM orders
WHERE status = 'delivered'
ORDER BY order_date DESC
LIMIT 3;

order_id | order_date
---------+-----------
114      | 2025-05-11
112      | 2025-04-18
110      | 2025-04-01
(3 rows)

If you ask for more rows than exist, you simply get all of them. LIMIT 50 on a 12-row result returns 12 rows, with no error.

OFFSET: skip some rows first

OFFSET m tells MySQL to skip the first m rows before it starts counting. This query skips the three most expensive products and returns the next three:

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

product_name        | price
--------------------+------
Graphics Tablet     | 4599
Mechanical Keyboard | 3499
Bluetooth Speaker   | 2499
(3 rows)

Compare it with the first example: these are positions 4, 5 and 6 of the same sorted list.

Pagination: showing results page by page

Suppose the product catalogue shows 4 products per page, sorted by name. Each page skips the rows of all the pages before it:

The rule is: offset = (page number - 1) × page size. Here is page 2:

SELECT product_id, product_name
FROM products
ORDER BY product_name, product_id
LIMIT 4 OFFSET 4;

product_id | product_name
-----------+--------------------
12         | Graphics Tablet
8          | HD Webcam
5          | Laptop Stand
2          | Mechanical Keyboard
(4 rows)

The product_id at the end of ORDER BY is a tiebreaker. If two products ever had the same name, their order would not be fixed, and a product could appear on two pages or on none. Sorting by a unique column as well makes every page stable.

In a PHP or Python app you work out the offset in your code and pass the two numbers to the query as integer parameters of a prepared statement. A page beyond the end, such as page 4 here, just returns zero rows, which your app can show as "No more products".

Why big offsets are slow

OFFSET 100000 does not jump straight to row 100,001. MySQL has to find and sort the first 100,000 rows, then throw them away. On a large table, page 5,000 of the results can be many times slower than page 1.

A faster pattern for "Next page" buttons is keyset pagination: remember the last value you showed and ask for rows after it. Suppose a list sorted by product_id shows 4 products per page, and page 2 ended with product 8. The next page is:

SELECT product_id, product_name
FROM products
WHERE product_id > 8
ORDER BY product_id
LIMIT 4;

product_id | product_name
-----------+---------------------
9          | 1TB External SSD
10         | 64GB Pen Drive
11         | Python Basics (Book)
12         | Graphics Tablet
(4 rows)

Because product_id is the primary key, it always has an index, so MySQL can jump straight to product_id > 8 and every page is as fast as the first. The cost is that users cannot jump to "page 37" directly. Many apps use OFFSET for the first few pages and keyset pagination for endless scrolling. You will learn about indexes in Indexes & EXPLAIN.

Common mistakes

• Using LIMIT without ORDER BY. SELECT * FROM orders LIMIT 5; returns some five rows, not necessarily the first or latest five. Always sort first.
• Putting the page number in OFFSET. For page 2 with 10 rows per page, the offset is 10, not 2. Use (page - 1) × size.
• Writing a calculation in LIMIT. MySQL accepts only plain whole numbers (or prepared-statement placeholders) after LIMIT and OFFSET. LIMIT 2 * 4 is a syntax error; work out the number in your app first.
• Putting LIMIT before ORDER BY. LIMIT must be the last clause of the query.

Key points

• LIMIT n returns at most n rows and is applied after WHERE and ORDER BY.
• OFFSET m skips the first m rows; together they make pagination.
• For page p with s rows per page, use LIMIT s OFFSET (p - 1) × s.
• Always sort with ORDER BY, and add a unique tiebreaker column for stable pages.
• In LIMIT 4, 4, the first number is the offset.
• Large offsets are slow; keyset pagination with WHERE id > last_id stays fast.

SELECT customer_id, first_name, joined_on
FROM customers
ORDER BY joined_on, customer_id
LIMIT 5 OFFSET 5;