Introduction

When you use LIMIT with OFFSET in MySQL, the database first determines the query result order, skips the first OFFSET rows from that ordered result, and then returns the next LIMIT rows. The important phrase there is "ordered result." Without ORDER BY, the rows you get are not guaranteed to be stable or meaningful across executions.

The Basic Rule

This query:

1SELECT id, name
2FROM users
3ORDER BY id
4LIMIT 3 OFFSET 2;

means:

1. sort the rows by id
2. skip the first 2
3. return the next 3

So if the ordered rows are:

1, 2, 3, 4, 5, 6, 7

then the returned rows are:

3, 4, 5

That is because the offset is zero-based. OFFSET 0 means "skip nothing."

Concrete Example

Suppose the users table contains:

1id  name
21   Ana
32   Ben
43   Cara
54   Dan
65   Eli
76   Fay

Then:

1SELECT id, name
2FROM users
3ORDER BY id
4LIMIT 2 OFFSET 3;

returns:

4  Dan
5  Eli

Why? Because MySQL orders the rows by id, skips 1, 2, and 3, then returns the next two rows.

Why ORDER BY Matters

Without ORDER BY, this query:

SELECT id, name
FROM users
LIMIT 2 OFFSET 3;

does not have a reliable answer in the logical SQL sense. Tables are unordered sets, so "the fourth row" only makes sense after you request a specific ordering.

In practice, you may observe a repeatable order on a small test table and be tempted to rely on it. That is a mistake. MySQL is free to return rows in whatever order fits the execution plan unless you specify otherwise.

So the correct mental model is:

• 'LIMIT and OFFSET act on the ordered result set'
• if you do not define the order, the paging result is unstable

Alternative Syntax

MySQL also supports the older comma syntax:

1SELECT id, name
2FROM users
3ORDER BY id
4LIMIT 3, 2;

That means the same thing as:

LIMIT 2 OFFSET 3

The first number is the offset, and the second is the row count.

Some developers prefer the explicit LIMIT ... OFFSET ... form because it reads more clearly.

Pagination Example

Suppose you want page 3 with page size 10. The offset is:

(page_number - 1) * page_size

So:

(3 - 1) * 10 = 20

The query becomes:

1SELECT *
2FROM orders
3ORDER BY created_at, id
4LIMIT 10 OFFSET 20;

That returns rows 21 through 30 from the ordered result set, not from the physical storage layout.

Rows Can Shift Between Requests

Another subtle point is that LIMIT and OFFSET pagination is sensitive to inserts and deletes.

If one row is inserted near the beginning between page requests, the rows on later pages can shift. That means page 3 fetched at one moment may not align with page 3 fetched later if the underlying data changed.

This is normal behavior. It is one reason large or highly active datasets often use keyset pagination instead of deep offsets.

Performance Implications

OFFSET does not magically jump to a row position for free. MySQL still has to work through the skipped rows in the ordered result.

For example:

1SELECT *
2FROM events
3ORDER BY created_at
4LIMIT 20 OFFSET 500000;

This can be much slower than smaller offsets because the engine still has to skip a large prefix of the result.

So LIMIT with OFFSET is easy to understand, but it is not always the most scalable pagination strategy.

Common Pitfalls

The biggest mistake is assuming LIMIT with OFFSET refers to table insertion order or storage order. It refers to row position in the query result, and that result only has a defined order if you use ORDER BY.

Another issue is forgetting that OFFSET is zero-based. OFFSET 2 starts returning from the third row of the ordered result.

Developers also often treat offset pagination as stable even while rows are being inserted or deleted. In reality, page contents can shift over time.

Finally, large offsets can be slow. If you need deep pagination on large datasets, consider keyset pagination instead of relying on OFFSET.

Summary

• MySQL first orders the query result, then skips OFFSET rows, then returns LIMIT rows.
• 'OFFSET is zero-based, so OFFSET 0 starts from the first row.'
• Without ORDER BY, the returned rows are not guaranteed to be stable.
• 'LIMIT 2 OFFSET 3 returns the fourth and fifth rows of the ordered result.'
• Deep offsets can be inefficient and can produce shifting pages when the underlying data changes.