MySQL LIMIT and OFFSET

MySQL LIMIT and OFFSET: Controlling Result Row Counts

The MySQL LIMIT clause restricts the maximum number of rows returned by a SELECT query. Adding OFFSET skips an initial number of rows, making LIMIT essential for pagination and top-N leaderboards.

Basic Syntax

You can pass a single count for top-N queries, or combine LIMIT with OFFSET for page slices:

SELECT column1, column2
FROM table_name
ORDER BY sort_column DESC
LIMIT row_count OFFSET skip_count;

MySQL also accepts the shorthand LIMIT skip_count, row_count, though LIMIT ... OFFSET ... is clearer to read and portable across PostgreSQL and SQLite.

Practical SQL Example

This query retrieves the top three highest-priced items in demo_products and then fetches page two.

-- Top 3 most expensive products
SELECT id, product_name, price
FROM demo_products
ORDER BY price DESC, id ASC
LIMIT 3;

-- Page 2 (skip first 3 rows, return next 3)
SELECT id, product_name, price
FROM demo_products
ORDER BY price DESC, id ASC
LIMIT 3 OFFSET 3;

Common Mistakes and Best Practices

• Always Pair LIMIT with Deterministic ORDER BY: Without ORDER BY, MySQL returns rows in arbitrary storage order that can shift between page loads. Add , id ASC as a tie-breaker when sorting non-unique columns like price or created_at.
• Avoid Deep OFFSET on Large Tables: LIMIT 20 OFFSET 500000 still scans and discards 500,000 rows. Use keyset (cursor) pagination instead: WHERE id < last_seen_id ORDER BY id DESC LIMIT 20.

Frequently Asked Questions

Does LIMIT work with UPDATE and DELETE in MySQL?

Yes, MySQL allows LIMIT n (without OFFSET) on UPDATE and DELETE to cap affected rows per batch.

Why do items repeat across pagination pages?

If the ORDER BY column has duplicate values without a unique tie-breaker like id, MySQL can shuffle tied rows across pages.

What happens if LIMIT is larger than the total number of rows?

MySQL simply returns all available matching rows without throwing an error.