The LIMIT clause is one of the first things anyone learns in MySQL, and also one of the most misused. I’ve seen “paginate through a million-row table” implemented with LIMIT 20 OFFSET 500000, run once per page load, and wondered why the site felt sluggish past page 50. LIMIT looks trivially simple, but how you combine it with ORDER BY, OFFSET, and indexes has a real, sometimes dramatic, effect on performance. This article covers LIMIT from the basics through to the patterns that actually scale.
What LIMIT Does
LIMIT restricts the number of rows a query returns. It’s applied after filtering (WHERE), grouping (GROUP BY/HAVING), and sorting (ORDER BY) — logically, it’s one of the last steps in query execution.
graph TD
A[FROM / JOIN] --> B[WHERE Filtering]
B --> C[GROUP BY / HAVING]
C --> D[ORDER BY Sorting]
D --> E[LIMIT / OFFSET]
E --> F[Final Result Set]
Understanding this order matters: LIMIT doesn’t reduce the amount of work done upstream unless the optimizer can push the limit down (which it sometimes can with an appropriate index — more on that below).
Basic Syntax
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 5;
Output:
+------------------+--------+
| product_name | price |
+------------------+--------+
| Gaming Laptop | 1899.99|
| 4K Monitor | 599.00 |
| Mechanical Keyboard| 149.99|
| Wireless Headset | 129.99 |
| USB-C Dock | 89.99 |
+------------------+--------+
LIMIT with OFFSET
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 5 OFFSET 10;
This skips the first 10 rows and returns the next 5. MySQL also supports a shorthand:
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 10, 5; -- equivalent to OFFSET 10 LIMIT 5
I’d note that the LIMIT offset, row_count syntax and LIMIT row_count OFFSET offset mean different things depending on argument order — I always use the explicit OFFSET keyword form in real code to avoid any ambiguity for whoever reads it later.
Pagination: The Common Use Case
-- Page 1 (rows 1-20)
SELECT * FROM articles ORDER BY published_at DESC LIMIT 20 OFFSET 0;
-- Page 2 (rows 21-40)
SELECT * FROM articles ORDER BY published_at DESC LIMIT 20 OFFSET 20;
-- Page 50 (rows 981-1000)
SELECT * FROM articles ORDER BY published_at DESC LIMIT 20 OFFSET 980;
This works correctly, but there’s a real performance problem hiding in it.
Why Deep OFFSET Is Slow
EXPLAIN SELECT * FROM articles ORDER BY published_at DESC LIMIT 20 OFFSET 980000;
Even though only 20 rows are returned, MySQL still has to walk through and discard the first 980,000 matching rows in sorted order before it can return the next 20 — OFFSET does not mean “skip cheaply,” it means “count and discard.” On a large table, this gets progressively slower the deeper you paginate.
+----+-------------+----------+-------+---------------+
| id | select_type | table | type | rows |
+----+-------------+----------+-------+---------------+
| 1 | SIMPLE | articles | index | 980020 |
+----+-------------+----------+-------+---------------+
The Better Pattern: Keyset (Seek) Pagination
Instead of counting rows to skip, keyset pagination remembers the last row seen and continues from there using an indexed WHERE condition:
-- First page
SELECT * FROM articles
ORDER BY published_at DESC, article_id DESC
LIMIT 20;
-- Next page: use the last row's published_at and article_id from the previous page
SELECT * FROM articles
WHERE (published_at, article_id) < ('2026-06-01 10:00:00', 48213)
ORDER BY published_at DESC, article_id DESC
LIMIT 20;
EXPLAIN SELECT * FROM articles
WHERE (published_at, article_id) < ('2026-06-01 10:00:00', 48213)
ORDER BY published_at DESC, article_id DESC
LIMIT 20;
+----+-------------+----------+-------+---------------------+------+
| id | select_type | table | type | possible_keys | rows |
+----+-------------+----------+-------+---------------------+------+
| 1 | SIMPLE | articles | range | idx_pubdate_id | 20 |
+----+-------------+----------+-------+---------------------+------+
Notice rows: 20 instead of nearly a million — this is a genuinely different execution plan, not just a syntactic variation, and the performance stays flat no matter how deep the user pages, as long as there’s a supporting composite index:
CREATE INDEX idx_pubdate_id ON articles (published_at DESC, article_id DESC);
LIMIT with JOINs
SELECT c.customer_name, o.order_id, o.total_amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_date DESC
LIMIT 10;
A subtlety worth knowing: LIMIT applies to the final joined result set, not to one side of the join before joining. If you want “the 10 most recent orders per customer” rather than “the 10 most recent order rows overall,” you need a different approach — a correlated subquery, a window function, or a lateral-join-style pattern:
SELECT customer_id, order_id, order_date, total_amount
FROM (
SELECT customer_id, order_id, order_date, total_amount,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
) ranked
WHERE rn <= 10;
LIMIT with UPDATE and DELETE
LIMIT isn’t just for SELECT — it’s also useful for controlled, incremental data modification:
DELETE FROM logs
WHERE created_at < '2025-01-01'
ORDER BY created_at
LIMIT 1000;
Running this in a loop lets you purge old data in small batches instead of one enormous DELETE that locks the table and bloats the undo log — a pattern I use constantly for cleaning up large historical tables without impacting live traffic.
UPDATE inventory
SET needs_review = TRUE
WHERE last_checked < '2025-06-01'
LIMIT 500;
LIMIT ALL and LIMIT with Expressions
SELECT * FROM products ORDER BY price LIMIT 5, 18446744073709551615; -- effectively "no upper limit" beyond offset
MySQL also allows LIMIT values to come from a prepared statement parameter (but notably, not directly from an arbitrary subquery expression in older versions):
PREPARE stmt FROM 'SELECT * FROM products ORDER BY price LIMIT ?';
SET @row_limit = 10;
EXECUTE stmt USING @row_limit;
Real-World Scenario: Infinite Scroll Feed
For a social-feed-style “infinite scroll” feature, keyset pagination is essentially mandatory for consistent performance at scale:
-- Initial load
SELECT post_id, content, created_at
FROM posts
WHERE user_id = 552
ORDER BY created_at DESC, post_id DESC
LIMIT 15;
-- Loading more, using the last seen post's timestamp/id
SELECT post_id, content, created_at
FROM posts
WHERE user_id = 552
AND (created_at, post_id) < ('2026-07-20 08:15:00', 91234)
ORDER BY created_at DESC, post_id DESC
LIMIT 15;
This scales the same whether the user is on page 2 or page 200 of their scroll history, unlike OFFSET-based pagination.
Performance and Optimization Tips
- Avoid deep OFFSET pagination for large tables — prefer keyset (seek) pagination whenever the UI allows for it (most “load more”/infinite scroll patterns do; traditional numbered page links are harder to convert).
- Always pair
ORDER BY ... LIMITwith a covering or supporting index that matches the sort order — without it, MySQL must sort the entire result set before applying LIMIT. - Use
LIMITwithDELETE/UPDATEto batch large data modifications and avoid long-held locks. - When you only need to check existence, use
LIMIT 1rather than fetching and counting a full result set:
SELECT 1 FROM orders WHERE customer_id = 4521 LIMIT 1;
- For “top N per group” queries, prefer window functions (
ROW_NUMBER()) over LIMIT inside a correlated subquery for both clarity and performance on modern MySQL (8.0+).
Security Considerations
- Never interpolate raw user input directly into the LIMIT/OFFSET values as string concatenation — even though LIMIT values are just numbers, unsanitized string-built SQL is still an SQL injection risk. Use parameterized queries or
PREPARE/EXECUTEwith bound parameters. - Validate that pagination parameters from client requests are within sane bounds (e.g., reject
LIMIT 500000from a public API) to prevent resource-exhaustion style abuse.
Troubleshooting Common Issues
Pagination gets slower on later pages. Classic symptom of OFFSET-based pagination on a large table — switch to keyset pagination as shown above.
LIMIT with ORDER BY returns inconsistent results across pages. This usually means the ORDER BY isn’t fully deterministic — if you’re sorting only by a column with duplicate values (like created_at alone, where multiple rows share the same timestamp), add a unique tiebreaker column like the primary key to guarantee stable ordering.
ORDER BY created_at DESC, post_id DESC -- id as tiebreaker avoids order ambiguity
LIMIT seems to be ignored inside a subquery in older MySQL versions. Certain older versions had restrictions on LIMIT within subqueries used with IN/ALL/ANY; this has been relaxed in modern MySQL, but always verify behavior with EXPLAIN against your specific version.
Frequently Asked Questions
What’s the difference between LIMIT 10 OFFSET 20 and LIMIT 20, 10? They’re equivalent — both skip 20 rows and return the next 10 — but the OFFSET keyword form is more explicit and less error-prone to read.
Is there a maximum value for LIMIT? It accepts very large unsigned integers (effectively unbounded numerically), but practically, application-level constraints should cap it well below anything that could return an unreasonable amount of data.
Does LIMIT without ORDER BY guarantee consistent rows across repeated runs? No. Without an ORDER BY, MySQL is free to return rows in any order, including different orders between executions. Always pair LIMIT with ORDER BY when consistent results matter.
Can LIMIT improve write performance too? Yes, when used with DELETE/UPDATE to batch large operations into smaller chunks, avoiding long lock durations and large transaction sizes.
Interview Questions
- Why does deep OFFSET pagination become slower as the offset increases, even though the number of returned rows stays the same?
- Explain keyset (seek) pagination and why it scales better than OFFSET-based pagination.
- Why should ORDER BY always include a unique tiebreaker column when used with LIMIT for pagination?
- How would you safely delete 10 million old rows from a live production table without causing a long lock?
- What’s the difference in behavior between
LIMIT 10, 20andLIMIT 20 OFFSET 10? - How would you retrieve the “top 3 orders per customer” efficiently in modern MySQL?
Summary and Key Takeaways
- LIMIT restricts the number of returned rows and is applied after filtering, grouping, and sorting.
- OFFSET-based pagination is simple but scales poorly on large tables because MySQL still has to count and discard skipped rows.
- Keyset (seek) pagination, using a WHERE condition on the last seen row, keeps performance flat regardless of how deep a user pages.
- LIMIT is also a powerful tool for batching UPDATE/DELETE operations to avoid long locks on large tables.
- Always pair ORDER BY with LIMIT and a unique tiebreaker column to guarantee stable, reproducible pagination.
LIMIT is deceptively simple syntax hiding a genuinely important architectural decision — how you paginate is often the difference between a feature that scales gracefully and one that quietly degrades as your data grows.