In our blog post on Optimizing SQL Pagination in Postgres, we explained how to optimize SQL pagination for Postgres. As a counterpart, we’ve decided to write one for MySQL pagination. Like PostgreSQL, MySQL offers several ways to handle pagination, but some are far more efficient than others.
Let's break down the SQL Pagination options, talk about why LIMIT and OFFSET can be a problem, and look at smarter ways to paginate large datasets efficiently.
The easiest way to paginate is using LIMIT and OFFSET. If you just need quick SQL pagination and you're working with small datasets, this works fine:
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 100;
But here’s the issue: OFFSET forces MySQL to do extra work. The database still scans and counts all the rows up to the offset before returning the results. This process slows down as you paginate further. For example, if you’re on page 1000, MySQL still processes the first 9990 rows just to discard them.
When dealing with thousands or millions of rows, queries using high offsets can slow down significantly. MySQL doesn’t optimize OFFSET well, and the cost of scanning unnecessary rows grows quickly. You can use tools like EXPLAIN to see how MySQL executes your query and why it’s slowing down.
Let’s look at an example:
mysql > EXPLAIN SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 300000;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+-------+
| 1 | SIMPLE | products | NULL | index | NULL | PRIMARY | 4 | NULL | 300010 | 100.00 | NULL |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)
The EXPLAIN output shows that MySQL is scanning over 300,010 rows due to the OFFSET, resulting in inefficient performance because it has to read all preceding rows before reaching the requested ones.
We can also demonstrate the performance difference. For instance, if we skip the first 10 rows, the query runs in milliseconds:
mysql > PAGER md5sum;
PAGER set to 'md5sum'
mysql > SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10;
5a8c7105aa4136d2677ee4b804bdaa61 -
10 rows in set (0.00 sec)
But if we skip 2 million rows, the performance drops significantly:
mysql > SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 2000000;
415dacbb2274af6c319ea58e1c5c8f0c -
10 rows in set (1.85 sec)
As we continue testing, we see the trend: just like in Postgres, the query time increases linearly, following an O(N) time complexity:
If the data isn’t cached by InnoDB Buffer Pool, the situation worsens. Here’s an example:
1. Cached Data:
mysql > EXPLAIN ANALYZE SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 8000000;
df5c3202c9e6da4cd33140b646307ff7 -
1 row in set (5.36 sec)
2. After a Cold Restart:
mysql > EXPLAIN ANALYZE SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 8000000;
c20eaf2103579022b1592aaf52c0b11e -
1 row in set (9.42 sec)
As we can see, there’s a 43% difference in performance:
Instead of using OFFSET, a better strategy is cursor based pagination (also known as keyset pagination). Instead of telling MySQL "skip X rows," you give it a starting point based on a known value:
SELECT * FROM products
WHERE id > 100
ORDER BY id
LIMIT 10;
This method SQL pagination is much faster because MySQL can use indexes efficiently. Instead of scanning thousands of rows, it jumps straight to the ones you need. This is how large platforms like Twitter and Facebook handle endless scrolling efficiently.
The EXPLAIN confirms the use of the PRIMARY key:
mysql > EXPLAIN SELECT * FROM products WHERE id > 10000 ORDER BY id LIMIT 10;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | products | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 8029493 | 100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
The EXPLAIN output shows MySQL efficiently uses the PRIMARY key index (type: range) for cursor based pagination, directly jumping to the relevant rows without scanning unnecessary data.
Cursor based pagination is much more efficient than LIMIT ... OFFSET ... because it leverages indexed lookups instead of scanning and discarding rows. Since we use the PRIMARY key, the time complexity is O(log N), which is far faster for larger datasets.
ReadySet is a caching engine that integrates seamlessly with MySQL. It sits between your application and the database, acting as a distributed caching layer to speed up queries, particularly those involving SQL pagination. Readyset automatically caches paginated query results and ensures the cached data stays in sync with the underlying MySQL database.
Key Benefits of ReadySet for MySQL:
• Automatic Caching – ReadySet caches paginated query results without requiring modifications to your application code. Queries using LIMIT and ORDER BY are optimized for performance.
• Incremental Updates – Unlike traditional caches, ReadySet efficiently updates cached data in real-time as changes occur in the MySQL database, ensuring consistency.
• Seamless Integration – ReadySet is wire-compatible with MySQL, meaning you only need to update your connection string to start using it—no changes to application code required.
Some SQL pagination pagination queries are not yet supported by ReadySet. For a full list of the limitations and unsupported query types, you can refer to the documentation here:
ReadySet Query Structure Limitations
If a query is unsupported, you’ll see a message similar to the following:
mysql> SHOW PROXIED QUERIES;
+--------------------+---------------------------------------------------------------------------------------------------------------------+--------------------+-------+
| query id | proxied query | readyset supported | count |
+--------------------+---------------------------------------------------------------------------------------------------------------------+--------------------+-------+
| q_e90f2e949bcc250f | SELECT `id`, `t` FROM `products` ORDER BY `id` | unsupported | 0 |
| q_de0bfa9ba7361ac9 | SELECT * FROM `products` ORDER BY `id` | unsupported | 0 |
+--------------------+---------------------------------------------------------------------------------------------------------------------+--------------------+-------+
If the query is supported, you can cache it using:
mysql> CREATE CACHE ALWAYS FROM SELECT * FROM products LIMIT 10 OFFSET 500000;
Query OK, 0 rows affected (1 min 18.54 sec)
On the first run, it will take more time to populate the cache:
mysql> SELECT * FROM products LIMIT 10 OFFSET 500000;
106365d91493106875182a8acec3afbf -
10 rows in set (19.61 sec)
Subsequent execution will be almost instant:
mysql> SELECT * FROM products LIMIT 10 OFFSET 500000;
106365d91493106875182a8acec3afbf -
10 rows in set (0.02 sec)
And the same query in MySQL:
mysql > SELECT * FROM products LIMIT 10 OFFSET 500000;
106365d91493106875182a8acec3afbf -
10 rows in set (0.69 sec)
Comparing the execution of the cached query in ReadySet against MySQL:
The query executed in ReadySet using OFFSET performs significantly faster (0.02 sec) than in MySQL (0.69 sec), representing approximately a 97.1% improvement in performance.
Lastly, to view the cached queries in ReadySet:
+--------------------+--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+-------+
| query id | cache name | query text | fallback behavior | count |
+--------------------+--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+-------+
| q_520dcc9f6b9bf17b | q_520dcc9f6b9bf17b | SELECT `test`.`products`.`id`, `test`.`products`.`s`, `test`.`products`.`t`, `test`.`products`.`g` FROM `test`.`products` WHERE (`test`.`products`.`s` = $1) | no fallback | 0 |
| q_f6bcc205268d573c | q_f6bcc205268d573c | SELECT `test`.`products`.`id`, `test`.`products`.`s`, `test`.`products`.`t`, `test`.`products`.`g` FROM `test`.`products` | no fallback | 0 |
+--------------------+--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+-------+
In conclusion, while there are different methods for SQL pagination, using LIMIT and OFFSET can lead to performance issues, especially with large datasets. Cursor based pagination provides a more efficient alternative by leveraging indexed lookups, reducing the overhead of scanning unnecessary rows. Additionally, integrating tools like ReadySet can significantly enhance SQL pagination performance by caching query results and ensuring consistency with the underlying database.
But, whether you are using pagination or not, Readyset's wire-compatible caching makes it easy to use low-latency queries and significantly boost the performance of your MySQL database without requiring changes to your application code. Sign up for Readyset Cloud for immediate access to SQL caching, or download Readyset to use on your own infrastructure.
Vassili Zarouba
Readyset