MySQLLIMIT & OFFSET

LIMIT in MySQL

The LIMIT clause restricts the number of rows returned by a SELECT, and optionally skips a number of rows first using an offset. It is essential for pagination, top-N queries, and protecting your application from accidentally loading millions of rows.

LIMIT Syntax Recap

MySQL supports three basic forms of the LIMIT clause:

  • LIMIT n — return at most n rows.
  • LIMIT offset, n — skip offset rows first, then return at most n rows (offset comes first!).
  • LIMIT n OFFSET m — return n rows after skipping m rows (count first, clearer syntax).

SQL
-- Return at most 10 rows
SELECT id, title, created_at FROM articles LIMIT 10;

-- Skip 20 rows, return the next 10 (two-argument form — offset is FIRST)
SELECT * FROM products ORDER BY id LIMIT 20, 10;

-- Same result with the clearer OFFSET keyword (count is FIRST)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

-- Without ORDER BY the result is non-deterministic
-- Always pair LIMIT with ORDER BY for predictable results
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 10;
Warning
In the two-argument form LIMIT offset, count the offset comes FIRST, which is the reverse of the LIMIT count OFFSET offset form. Many developers swap them by mistake. The LIMIT n OFFSET m form is clearer and recommended.
Page 1 / Page 2 / Page N Pattern

Classic offset pagination maps page numbers to SQL offsets. Given a page size of page_size and a 1-indexed page number page, the offset is (page - 1) * page_size.

SQL
-- page_size = 10, pages are 1-indexed
-- Page 1: OFFSET 0
SELECT id, title, excerpt, published_at
FROM posts
WHERE status = 'published'
ORDER BY published_at DESC, id DESC
LIMIT 10 OFFSET 0;

-- Page 2: OFFSET 10
SELECT id, title, excerpt, published_at
FROM posts
WHERE status = 'published'
ORDER BY published_at DESC, id DESC
LIMIT 10 OFFSET 10;

-- Page 5: OFFSET (5-1)*10 = 40
SELECT id, title, excerpt, published_at
FROM posts
WHERE status = 'published'
ORDER BY published_at DESC, id DESC
LIMIT 10 OFFSET 40;

-- Total page count requires a separate COUNT query
SELECT COUNT(*) AS total FROM posts WHERE status = 'published';
-- total_pages = CEIL(total / page_size)
Why Deep OFFSET Is Slow

MySQL cannot teleport to row 100 000. To execute LIMIT 10 OFFSET 100000, the engine must scan and discard 100 000 rows before returning the 10 rows you actually want. The work grows linearly with the offset.

SQL
-- This forces MySQL to scan ~100 010 rows and discard 100 000
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;

-- EXPLAIN reveals the cost
EXPLAIN SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;
-- rows: ~100010   type: index   Extra: Using index
Tip
Use keyset (cursor) pagination for datasets with millions of rows or for features requiring "infinite scroll". Keyset pagination is O(1) regardless of page depth.
Keyset (Cursor) Pagination

Instead of skipping rows by count, keyset pagination remembers the last seen value and filters by it. The database uses an index to seek directly to the right position — no rows are scanned and discarded.

SQL
-- First page: no cursor yet
SELECT id, title, published_at
FROM articles
ORDER BY published_at DESC, id DESC
LIMIT 10;
-- The client stores the last row values: published_at='2024-06-15', id=842

-- Next page: pass the cursor values as a WHERE predicate
SELECT id, title, published_at
FROM articles
WHERE (published_at < '2024-06-15')
   OR (published_at = '2024-06-15' AND id < 842)
ORDER BY published_at DESC, id DESC
LIMIT 10;

-- Equivalent using row value constructor (MySQL 8.0+)
SELECT id, title, published_at
FROM articles
WHERE (published_at, id) < ('2024-06-15', 842)
ORDER BY published_at DESC, id DESC
LIMIT 10;

-- This uses the index on (published_at, id) for an O(1) seek
Note
Keyset pagination does not support jumping to an arbitrary page number — only sequential next/previous navigation. If users need "jump to page 47", use offset pagination but limit the maximum offset, or cache the page break values in advance.
Keyset vs Offset: Advantages

Feature

OFFSET Pagination

Keyset Pagination

SQL

LIMIT n OFFSET m

WHERE (col) < cursor LIMIT n

Performance on deep pages

O(offset) — grows linearly

O(1) — always fast

Jump to arbitrary page

Yes

No

Stable under concurrent inserts

No — rows may skip or repeat

Yes — cursor is stable

Requires unique sort key

No

Yes (PK or unique column)

Total page count

Easy (COUNT(*) / page_size)

Not straightforward

Complexity

Simple

Slightly more complex

Why ORDER BY Is Required for Stable Pagination

Without ORDER BY, the database is free to return rows in any order — which may change between executions depending on storage engine internals, parallel execution plans, or buffer pool state. Pagination without ORDER BY produces random, inconsistent pages.

SQL
-- BAD: row order is undefined, pages will be inconsistent
SELECT id, name FROM customers LIMIT 10 OFFSET 20;

-- GOOD: deterministic order guaranteed by ORDER BY
SELECT id, name FROM customers ORDER BY id LIMIT 10 OFFSET 20;

-- For pagination stability, the ORDER BY key must be unique.
-- If ordering by a non-unique column (e.g. created_at), add the PK as a tiebreaker:
SELECT id, name, created_at
FROM customers
ORDER BY created_at DESC, id DESC
LIMIT 10 OFFSET 20;
LIMIT in UPDATE and DELETE (Safe Batching)

Both UPDATE and DELETE accept LIMIT. This is invaluable for large table operations because it keeps each individual transaction small, reducing lock duration and replication lag.

SQL
-- Delete expired sessions in batches of 1000
DELETE FROM sessions
WHERE expires_at < NOW()
ORDER BY expires_at
LIMIT 1000;

-- Update pending notifications in batches of 500
UPDATE notifications
SET status = 'sent', sent_at = NOW()
WHERE status = 'queued'
ORDER BY created_at
LIMIT 500;

Bash
#!/bin/bash
# Shell loop: keep deleting until fewer than 1000 rows are affected
while true; do
  ROWS=$(mysql mydb -sN -e "
    DELETE FROM audit_logs
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR)
    ORDER BY created_at LIMIT 1000;
    SELECT ROW_COUNT();
  ")
  echo "Deleted ${ROWS} rows"
  [ "${ROWS}" -lt 1000 ] && break
  sleep 0.1
done
Tip
Batch deletes with LIMIT are the safe way to purge large volumes of data in production. Each batch completes in milliseconds, keeping locks short and replication lag minimal.
LIMIT in Subqueries and Derived Tables

SQL
-- Top-3 most expensive products (derived table with LIMIT)
SELECT * FROM (
  SELECT id, name, price
  FROM products
  ORDER BY price DESC
  LIMIT 3
) AS top3;

-- Top-1 most recent order per customer (lateral-style using subquery)
SELECT c.id, c.name,
  (SELECT id FROM orders WHERE customer_id = c.id
   ORDER BY created_at DESC LIMIT 1) AS latest_order_id
FROM customers c;

-- Modern approach: ROW_NUMBER() with window function (MySQL 8.0+)
SELECT customer_id, id AS order_id, total, created_at
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders
) ranked
WHERE rn = 1;
SQL_CALC_FOUND_ROWS and FOUND_ROWS()

Before MySQL 8.0, a common pattern paired SQL_CALC_FOUND_ROWS with FOUND_ROWS() to get the total row count of a paginated query in one pass. This avoided running a separate COUNT(*) query.

SQL
-- Legacy pattern (deprecated in MySQL 8.0.17)
SELECT SQL_CALC_FOUND_ROWS id, title
FROM articles
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 10 OFFSET 20;

-- Immediately after: get total rows (ignoring LIMIT)
SELECT FOUND_ROWS() AS total_rows;
Warning
SQL_CALC_FOUND_ROWS was deprecated in MySQL 8.0.17 and removed in MySQL 9.0. For new code, run a separate COUNT(*) query or cache the total count. The deprecated pattern also had a performance problem: MySQL still evaluated the full result set before applying LIMIT, which was no faster than a separate COUNT.
Practical REST API Pagination Example

A REST API endpoint GET /api/posts?page=3&size=20 can be implemented with either strategy:

SQL
-- Offset-based (page parameter from client)
-- page=3, size=20 -> OFFSET = (3-1)*20 = 40
SELECT id, title, published_at, author_id
FROM posts
WHERE status = 'published'
ORDER BY published_at DESC, id DESC
LIMIT 20 OFFSET 40;

-- Total count for X-Total-Count header
SELECT COUNT(*) FROM posts WHERE status = 'published';

-- Cursor-based (cursor parameter = last seen id from previous response)
-- GET /api/posts?cursor=last_id=501&last_ts=2024-06-10&size=20
SELECT id, title, published_at, author_id
FROM posts
WHERE status = 'published'
  AND (published_at < '2024-06-10'
       OR (published_at = '2024-06-10' AND id < 501))
ORDER BY published_at DESC, id DESC
LIMIT 20;
-- Response includes next_cursor pointing to the last row's (published_at, id)

  1. Offset pagination: simple to implement, easy to add a page count, but becomes slow beyond page ~1000 on a large table.

  2. Cursor pagination: scales to billions of rows, stable under concurrent inserts, but requires the client to maintain a cursor token and cannot jump to an arbitrary page.

LIMIT with Top-N Queries

One of the most common uses of LIMIT is selecting the "top N" rows — the highest, lowest, newest, or most popular records from a larger set.

SQL
-- Top 5 best-selling products this month
SELECT p.id, p.name, SUM(oi.quantity) AS units_sold
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN orders o ON oi.order_id = o.id
WHERE o.created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
GROUP BY p.id, p.name
ORDER BY units_sold DESC
LIMIT 5;

-- 3 most recent active users per department
SELECT *
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY last_login DESC) AS rn
  FROM users
  WHERE is_active = 1
) ranked
WHERE rn <= 3;

-- Single row: record with highest value (faster than ORDER BY + LIMIT 1 on large tables)
SELECT * FROM orders WHERE total = (SELECT MAX(total) FROM orders) LIMIT 1;
LIMIT and Index Interaction (EXPLAIN Analysis)

MySQL's optimizer considers LIMIT when choosing execution plans. Adding LIMIT can actually change the chosen index and access method because the optimizer knows it only needs N rows rather than the entire table.

SQL
-- Without LIMIT: optimizer may choose a covering index for a full scan
EXPLAIN SELECT id, status FROM orders ORDER BY created_at DESC;
-- type: index   (full index scan)

-- With LIMIT: optimizer can use an index range and stop early
EXPLAIN SELECT id, status FROM orders ORDER BY created_at DESC LIMIT 10;
-- type: index   rows: 10   (stops after first 10 matching rows)

-- Adding a composite index that covers ORDER BY and SELECT columns
-- eliminates the need for a filesort entirely
ALTER TABLE orders ADD INDEX idx_created_covering (created_at DESC, id, status);

EXPLAIN SELECT id, status FROM orders ORDER BY created_at DESC LIMIT 10;
-- Extra: 'Using index'  (all data comes from the index, no row lookup needed)

-- WARNING: LIMIT without ORDER BY may lead the optimizer to pick unexpected plans
-- Always specify ORDER BY to get deterministic query plans
LIMIT in Stored Procedures and Prepared Statements

SQL
-- Stored procedure with dynamic LIMIT
DELIMITER $$
CREATE PROCEDURE get_recent_orders(IN p_limit INT, IN p_offset INT)
BEGIN
  SELECT id, customer_id, total, created_at
  FROM orders
  ORDER BY created_at DESC
  LIMIT p_limit OFFSET p_offset;
END$$
DELIMITER ;

-- Call with specific page parameters
CALL get_recent_orders(25, 0);   -- page 1
CALL get_recent_orders(25, 25);  -- page 2

-- Prepared statement with LIMIT parameters (useful for dynamic pagination)
PREPARE stmt FROM
  'SELECT id, title FROM articles ORDER BY published_at DESC LIMIT ? OFFSET ?';
SET @page_size = 10, @offset = 30;
EXECUTE stmt USING @page_size, @offset;
DEALLOCATE PREPARE stmt;
Avoiding Common LIMIT Mistakes

Mistake

Problem

Fix

LIMIT without ORDER BY

Non-deterministic results — different rows returned on each execution

Always add ORDER BY before LIMIT

LIMIT 20, 10 argument order confusion

Developers swap offset and count

Use LIMIT 10 OFFSET 20 for clarity

SELECT * with LIMIT on a wide table

Fetches all columns needlessly; LIMIT only reduces row count, not column count

Select only required columns

Deep OFFSET on millions of rows

O(offset) performance — scans and discards all skipped rows

Switch to keyset pagination

SQL_CALC_FOUND_ROWS still in new code

Deprecated in 8.0.17, removed in 9.0

Use a separate COUNT(*) query

LIMIT in subquery without ORDER BY

MySQL may ignore or reorder the result unpredictably

Add ORDER BY inside the subquery

LIMIT Behaviour in Views and Derived Tables

SQL
-- MySQL may ignore LIMIT inside a view definition in some contexts
-- Safe pattern: apply LIMIT in the outer query, not inside the view
CREATE VIEW active_products AS
  SELECT id, name, price FROM products WHERE active = 1;

-- Apply LIMIT in the consuming query
SELECT * FROM active_products ORDER BY price DESC LIMIT 10;

-- Derived table with LIMIT (ORDER BY required for MySQL to honour LIMIT in subquery)
SELECT * FROM (
  SELECT id, name, price
  FROM products
  WHERE active = 1
  ORDER BY price DESC
  LIMIT 10
) AS top_products
ORDER BY name;
Note
Prior to MySQL 8.0, LIMIT inside a view or subquery was sometimes silently ignored unless accompanied by an ORDER BY clause. Always include ORDER BY alongside LIMIT in subqueries to guarantee the intended rows are selected.
LIMIT and Performance: Index Design Guidelines

The performance of a LIMIT query is largely determined by whether MySQL can satisfy both the ORDER BY and the WHERE clause using a single index. When it can, LIMIT is O(1) per page — MySQL reads exactly N index entries and stops. When it cannot, MySQL may perform a filesort of the entire result set before applying LIMIT.

SQL
-- Scenario: paginate published posts, newest first
-- Ideal composite index covers: WHERE status (equality) + ORDER BY published_at + id
ALTER TABLE posts ADD INDEX idx_status_pub (status, published_at DESC, id DESC);

EXPLAIN SELECT id, title, published_at
FROM posts
WHERE status = 'published'
ORDER BY published_at DESC, id DESC
LIMIT 10 OFFSET 0;
-- type: ref   key: idx_status_pub   Extra: 'Using index'

-- Without the index, MySQL must:
-- 1. Full scan all published posts (e.g. 500 000 rows)
-- 2. Sort them all by published_at DESC
-- 3. Pick the first 10
-- With the index it reads exactly 10 index entries and stops
Pagination Token Pattern for APIs

REST APIs often encode the pagination cursor as an opaque token to hide implementation details and allow the backend to change the pagination strategy without breaking clients.

SQL
-- The token encodes the last-seen values, base64-encoded on the server side
-- Token 'eyJpZCI6ODQyLCJ0cyI6IjIwMjQtMDYtMTUifQ==' decodes to:
-- {"id": 842, "ts": "2024-06-15"}

-- Server-side query using the decoded cursor
SELECT id, title, published_at
FROM articles
WHERE status = 'published'
  AND (published_at < '2024-06-15'
       OR (published_at = '2024-06-15' AND id < 842))
ORDER BY published_at DESC, id DESC
LIMIT 20;

-- The API response includes:
-- {
--   "data": [...],
--   "next_cursor": "eyJpZCI6ODIxLCJ0cyI6IjIwMjQtMDYtMTQifQ==",
--   "has_more": true
-- }
-- Clients pass next_cursor as a query param: GET /api/articles?cursor=<token>
Best Practices

  • Always pair LIMIT with ORDER BY — LIMIT without ORDER BY returns rows in an undefined order.

  • Use LIMIT n OFFSET m syntax (not LIMIT m, n) for clarity and to avoid swapping arguments.

  • Switch to keyset pagination for tables with millions of rows or deep page navigation.

  • Add the ORDER BY column(s) to an index to avoid filesort with LIMIT.

  • Use LIMIT in DELETE and UPDATE batch jobs to keep individual transactions short.

  • Never use LIMIT without a WHERE in analytics queries — add appropriate date filters first.

  • Store the total row count separately (or cache it) rather than running COUNT(*) on every page request.

  • Avoid SQL_CALC_FOUND_ROWS — it is deprecated; use a separate COUNT(*) query instead.

  • For top-N queries consider a covering index that includes the ORDER BY column and all SELECT columns.