Introduction

In MySQL, OFFSET does not mean "skip rows and then return everything forever." It is only part of a LIMIT clause, so you always need some row count in the SQL syntax. If your goal is "skip the first N rows and return the rest," the practical answers are either to use a very large limit or, better, rewrite the query so it does not depend on large offsets at all.

How LIMIT and OFFSET Work Together

MySQL pagination is usually written in one of these forms:

1SELECT *
2FROM products
3ORDER BY id
4LIMIT 20 OFFSET 40;

or:

1SELECT *
2FROM products
3ORDER BY id
4LIMIT 40, 20;

Both mean "skip 40 rows, then return 20 rows."

The important detail is that OFFSET is not a stand-alone clause. It modifies a LIMIT operation.

There Is No Literal "Infinite Rows" Keyword

MySQL does not provide syntax like:

OFFSET 40 ALL

or:

OFFSET 40 INFINITE

If you want everything after a certain offset, the common workaround is a very large limit:

1SELECT *
2FROM products
3ORDER BY id
4LIMIT 18446744073709551615 OFFSET 40;

That large number is the maximum unsigned BIGINT value MySQL accepts in this context. It effectively means "skip 40, then return the rest."

The query is valid, but it is often not the best design.

Why Large Offsets Are a Performance Problem

Offset pagination gets slower as the offset grows. Even if you only return a small number of rows, MySQL still has to locate and skip the preceding rows in the ordered result set.

For example:

1SELECT *
2FROM products
3ORDER BY id
4LIMIT 20 OFFSET 500000;

This can be much more expensive than page 1, because the server still has to walk through a very large prefix of the result.

That is why "skip a big number, then return the rest" is often a smell. It works syntactically, but it is not efficient on large tables.

Prefer Keyset Pagination When You Can

If you are paginating through a stable ordered column such as an indexed id, keyset pagination is usually better:

1SELECT *
2FROM products
3WHERE id > 500000
4ORDER BY id
5LIMIT 20;

Instead of saying "skip 500000 rows," you say "start after this known last key." That makes the query easier for MySQL to optimize, especially with a supporting index.

This approach is often called:

• keyset pagination
• seek pagination
• cursor-style pagination

It is generally the better choice for large datasets or user-facing APIs.

If You Truly Need "Everything After Row N"

Sometimes the request really is positional rather than key-based. Maybe the sort order is complex, or the result must match a specific offset-based contract.

In that case, a large limit is the direct SQL answer:

1SELECT *
2FROM products
3ORDER BY created_at, id
4LIMIT 18446744073709551615 OFFSET 40;

Just be clear about the tradeoff:

• it solves the syntax problem
• it does not solve offset scalability

Also, always include ORDER BY. Without it, "skip the first 40" has no stable meaning because SQL tables do not guarantee row order unless you request one.

When Omitting LIMIT Does Not Help

Developers sometimes try to write:

1SELECT *
2FROM products
3ORDER BY id
4OFFSET 40;

That is not valid MySQL syntax. OFFSET belongs with LIMIT.

So if your mental model is "OFFSET should be independent," that is where the confusion usually starts.

Choosing the Right Pagination Strategy

Use offset pagination when:

• page numbers matter to the UI
• the dataset is modest
• exact positional paging is acceptable

Use keyset pagination when:

• performance matters
• pages may be deep
• an ordered key is available
• consistency across inserts and deletes matters

Offset pagination can also produce surprising user experiences when rows are inserted or deleted between requests. A later page may shift unexpectedly. Keyset pagination is usually more stable in that situation.

Common Pitfalls

The most common mistake is expecting OFFSET to work without LIMIT. In MySQL, it does not.

Another issue is using very large offsets in production and assuming an index alone makes that cheap. The database still has to skip many rows in order.

Developers also sometimes omit ORDER BY, which makes offset-based paging unstable and potentially meaningless.

Finally, a huge LIMIT solves the syntax problem for "return the rest," but it does not make the query a good pagination strategy for large result sets.

Summary

• In MySQL, OFFSET is part of LIMIT, not a separate clause.
• There is no built-in "infinite rows" keyword for OFFSET.
• To skip N rows and return the rest, you can use a very large LIMIT.
• Large offsets are often slow, even with indexing.
• For scalable pagination, prefer keyset pagination over deep offset pagination when possible.