MySQL LIMIT: Pagination, OFFSET, and Performance Pitfalls

Q: What's the difference between LIMIT 10 and LIMIT 0, 10?

Same result - first 10 rows. The `LIMIT 0, 10` form is shorthand for `LIMIT 10 OFFSET 0`. The team that uses the two-argument form needs to remember offset comes first.

Q: How do I get a random sample?

`SELECT * FROM products ORDER BY RAND() LIMIT 10`. Slow on large tables. The team that uses `RAND()` for small tables is fine; large tables should use other approaches (offset into known IDs, TABLESAMPLE on other databases).

Q: How does LIMIT work with OFFSET 0?

Returns first N rows. Same as `LIMIT N`. The team that uses `LIMIT N OFFSET 0` is explicit; the team that uses `LIMIT N` is concise.

MySQL LIMIT limits rows returned: LIMIT 10 (first 10), LIMIT 5, 10 (skip 5, return 10 - useful for pagination). Deep OFFSETs (LIMIT 1000000, 10) are slow because MySQL scans and discards the offset rows. The team that uses keyset pagination (WHERE id > last_id LIMIT 10) for large datasets has consistent performance. The team that uses OFFSET for pagination beyond page ~100 has slow queries.

Basic LIMIT
LIMIT with offset
Pagination pattern
The OFFSET performance pitfall
Keyset pagination (the fix)
OFFSET for shallow pagination
LIMIT in subqueries
FAQ

Basic LIMIT

SELECT * FROM products LIMIT 10;

Returns the first 10 rows. With ORDER BY:

SELECT * FROM products ORDER BY created_at DESC LIMIT 10;

Returns the 10 most recent. The team that uses LIMIT 10 for a ‘recent items’ widget has the right query.

LIMIT with offset

SELECT * FROM products LIMIT 10 OFFSET 20;

-- Or shorthand (single argument):
SELECT * FROM products LIMIT 20, 10;  -- skip 20, return 10

Returns rows 21-30. The team that uses this for page 3 of pagination has correct math (offset = (page - 1) * page_size).

Pagination pattern

page = int(request.GET.get('page', 1))
page_size = 20
offset = (page - 1) * page_size

cursor.execute("SELECT * FROM products LIMIT %s OFFSET %s", (page_size, offset))

The team that uses page/offset has standard pagination UI. The team that pages beyond 1000+ has performance issues.

The OFFSET performance pitfall

-- Page 100,000 of a million-row table:
SELECT * FROM products LIMIT 20 OFFSET 1999980;

MySQL scans and discards 1,999,980 rows to return 20. Time: seconds. The team that uses deep OFFSET has slow queries at scale.

Keyset pagination (the fix)

Use WHERE to filter past the last seen key:

-- First page
SELECT * FROM products ORDER BY id ASC LIMIT 20;

-- Next page (use the last id from previous page)
SELECT * FROM products WHERE id > 19980 ORDER BY id ASC LIMIT 20;

The team that uses keyset pagination has consistent speed regardless of page depth. The trade-off: client needs to track the last ID, no random page access.

OFFSET for shallow pagination

For UI with ‘page 1, 2, 3, … 50’ (not millions of pages), OFFSET is fine:

SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 1000;

The team that has a small page count uses OFFSET. The team that has ‘infinite scroll’ uses keyset.

LIMIT in subqueries

SELECT * FROM products WHERE id IN (
  SELECT product_id FROM orders ORDER BY created_at DESC LIMIT 100
);

The subquery finds the 100 most recent order product IDs, outer query gets full product details. The team that uses subqueries for top-N lookups has efficient queries.

FAQ

What’s the difference between LIMIT 10 and LIMIT 0, 10?

Same result - first 10 rows. The LIMIT 0, 10 form is shorthand for LIMIT 10 OFFSET 0. The team that uses the two-argument form needs to remember offset comes first.

Can LIMIT be used without ORDER BY?

Yes but the result is undefined (whichever rows MySQL reads first). The team that uses LIMIT without ORDER BY has non-deterministic results.

How do I get a random sample?

SELECT * FROM products ORDER BY RAND() LIMIT 10. Slow on large tables. The team that uses RAND() for small tables is fine; large tables should use other approaches (offset into known IDs, TABLESAMPLE on other databases).

Does LIMIT improve performance?

Sometimes - MySQL stops reading after N rows. But if there’s a filter or ORDER BY that needs to scan the whole table, LIMIT doesn’t help. The team that uses LIMIT for a filtered query that returns 1000s of rows still has the filter cost.

How does LIMIT work with OFFSET 0?

Returns first N rows. Same as LIMIT N. The team that uses LIMIT N OFFSET 0 is explicit; the team that uses LIMIT N is concise.

If you are sizing the infrastructure for the kind of project this post covers, the RunxBuild hosting calculator is the right place to model the line items. The compute, the memory, the storage, the bandwidth, the database - each one is a separate number, and the team’s mental model for the platform is the sum of those numbers. The RunxBuild dashboard is where the team sees the actual usage in one place.

Useful related references: