MySQL Pagination - Deepak Paswan

A list page rarely shows all rows. It shows one page at a time. There are two ways to do that in MySQL.

query.sql

SQL

SET SESSION cte_max_recursion_depth = 20000;

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(40) NOT NULL,
    published_at DATETIME NOT NULL,
    KEY idx_published (published_at, id)
);
INSERT INTO articles (title, published_at)
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 10000)
SELECT CONCAT('Article ', i), '2026-01-01 00:00:00' + INTERVAL i MINUTE FROM n;

Method 1: LIMIT and OFFSET

Page number p with size rows per page: LIMIT size OFFSET (p - 1) * size.

query.sql

SQL

1
2

SELECT id, title FROM articles ORDER BY published_at DESC, id DESC LIMIT 5 OFFSET 0;
SELECT id, title FROM articles ORDER BY published_at DESC, id DESC LIMIT 5 OFFSET 5;

Output

OUTPUT

+-------+---------------+
| id    | title         |
+-------+---------------+
| 10000 | Article 10000 |
|  9999 | Article 9999  |
|  9998 | Article 9998  |
|  9997 | Article 9997  |
|  9996 | Article 9996  |
+-------+---------------+
5 rows in set

+------+--------------+
| id   | title        |
+------+--------------+
| 9995 | Article 9995 |
| 9994 | Article 9994 |
| 9993 | Article 9993 |
| 9992 | Article 9992 |
| 9991 | Article 9991 |
+------+--------------+
5 rows in set

It is simple, and it lets users jump to page 37. But there are two problems.

Problem 1: deep pages are slow. OFFSET 100000 makes MySQL read and throw away 100,000 rows before it returns the page you want. The cost grows with the page number.

Problem 2: pages shift. If a new article is published while a reader moves from page 1 to page 2, everything moves by one row. The reader sees the last item of page 1 again at the top of page 2, or misses one.

Method 2: keyset (seek) pagination

Instead of “skip N rows”, say “give me the rows after the last one I saw“. The client remembers the sort values of the last row on the page.

query.sql

SQL

SELECT id, title, published_at FROM articles ORDER BY published_at DESC, id DESC LIMIT 3;

Output

OUTPUT

+-------+---------------+---------------------+
| id    | title         | published_at        |
+-------+---------------+---------------------+
| 10000 | Article 10000 | 2026-01-07 22:40:00 |
|  9999 | Article 9999  | 2026-01-07 22:39:00 |
|  9998 | Article 9998  | 2026-01-07 22:38:00 |
+-------+---------------+---------------------+
3 rows in set

The last row is article 10000 (published 2026-01-07 22:40:00). To get the next page:

query.sql

SQL

SELECT id, title, published_at
FROM articles
WHERE (published_at, id) < ('2026-01-07 22:40:00', 10000)
ORDER BY published_at DESC, id DESC
LIMIT 3;

Output

OUTPUT

+------+--------------+---------------------+
| id   | title        | published_at        |
+------+--------------+---------------------+
| 9999 | Article 9999 | 2026-01-07 22:39:00 |
| 9998 | Article 9998 | 2026-01-07 22:38:00 |
| 9997 | Article 9997 | 2026-01-07 22:37:00 |
+------+--------------+---------------------+
3 rows in set

The row-value comparison (published_at, id) < (x, y) means “earlier than x, or the same time and a smaller id”. MySQL uses the index idx_published (published_at, id) to jump right to that position, and reads only 3 rows, even on page 10,000.

	OFFSET	Keyset
Simple to write	Yes	A bit harder
Jump to page N	Yes	No (only next and previous)
Speed on deep pages	Slow	Always fast
Stable if rows are added	No	Yes
Needs a unique sort key	Recommended	Required
Best for	Admin tables, small data	Feeds, infinite scroll, APIs, big tables

The tie-breaker is required

If two rows can have the same published_at, the keyset query needs id as a second key. Otherwise rows with equal timestamps are skipped or repeated. Every pagination query needs a unique, complete sort order.

How MySQL runs them

query.sql

SQL

1
2

EXPLAIN FORMAT=TRADITIONAL SELECT id FROM articles ORDER BY published_at DESC, id DESC LIMIT 5 OFFSET 9000;
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM articles WHERE (published_at, id) < ('2026-01-01 10:00:00', 600) ORDER BY published_at DESC, id DESC LIMIT 5;

Output

OUTPUT

+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+----------------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | articles | NULL       | index | NULL          | idx_published | 9       | NULL | 9005 |   100.00 | Backward index scan; Using index |
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+----------------------------------+
1 row in set

+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                         |
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------------------------------+
|  1 | SIMPLE      | articles | NULL       | index | NULL          | idx_published | 9       | NULL |    5 |   100.00 | Using where; Backward index scan; Using index |
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------------------------------+
1 row in set

Both use the index. The OFFSET query still walks 9,005 index entries in order. The keyset query starts at the right position and stops after 5. (rows is an estimate and EXPLAIN does not show the OFFSET cost clearly. Time both on a big table to see it.)

Total counts

A page usually shows “page 3 of 120”. You need the total:

query.sql

SQL

SELECT COUNT(*) AS total FROM articles;

Output

OUTPUT

+-------+
| total |
+-------+
| 10000 |
+-------+
1 row in set

On a big InnoDB table, COUNT() has to scan an index and can be slow. Options: cache the count, show only “next / previous”, show an approximate count (information_schema.tables.table_rows), or count only up to a limit (SELECT COUNT() FROM (SELECT 1 FROM articles LIMIT 1001) t) and show “1000+”.

Older code uses SQL_CALC_FOUND_ROWS and FOUND_ROWS(). They are deprecated since MySQL 8.0.17: they force a full scan anyway, and are slower than a separate COUNT(*). Do not use them.

Which to choose

A back-office table with a few thousand rows and page numbers: OFFSET is fine.
A public feed, an API, or any table that can grow into millions: keyset.
A search result with relevance ranking: limit the depth (nobody reads page 500) and use OFFSET with a cap.

lightbulb

Cap the depth

Even with OFFSET, refuse pages beyond a reasonable limit (for example page 100). It protects the server from scrapers that walk every page.