When I first started learning programming, I followed examples in textbooks using LIMIT offset, row_count for pagination. However, as the volume of data increased, this pagination method caused longer query times for later pages. In MySQL, when an offset is specified, it does not directly start retrieving data from that offset. Instead, it fetches all data based on the ‘where’ conditions first, and then retrieves the required number of rows beginning from the offset.
This means, for example, if there are a million results satisfying the ‘where’ conditions, and you want to just retrieve the last 1,000, using LIMIT 999000, 1000, MySQL will still first find all one million records, and then extract the last 1,000 from the 999,000th record onwards. This results in slower query speeds as the amount of data increases.
A better way is to not specify an offset, but only specify a limit to restrict the number of records.
For example:
LIMIT 1000
For the offset part, use conditions like Unique Key, Primary Key, etc., to locate the needed data, combined with ORDER BY to ensure correct pagination. This way, you start retrieving the required number of records right after the data you have already found.
Take the user data as an example (where user_account is the PK). If the last record of user data on the previous page is brandy, the next page can start fetching from brandy.
SELECT
user_account,
first_name,
last_name
FROM
user
WHERE
user_account > 'brandy'
ORDER BY
user_account ASC
LIMIT
1000
The difference between LIMIT offset, row_count and LIMIT row_count can be seen by comparing them with EXPLAIN.
Before improvement:
EXPLAIN SELECT
user_account,
first_name,
last_name
FROM
user
ORDER BY
user_account ASC
LIMIT
999000, 1000;
+----+-------------+--------------+-------+---------------+---------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+---------------+---------+---------+------+---------+-------+
| 1 | SIMPLE | user_account | index | NULL | PRIMARY | 100 | NULL | 1000000 | NULL |
+----+-------------+--------------+-------+---------------+---------+---------+------+---------+-------+
It is clear that even with an offset specified, MySQL still found 1,000,000 records.
After improvement:
EXPLAIN SELECT
user_account,
first_name,
last_name
FROM
user
WHERE
user_account > 'GeorgeMartin'
ORDER BY
user_account ASC
LIMIT
1000;
+----+-------------+--------------+-------+---------------+---------------+---------+------+--------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+---------------+---------------+---------+------+--------+-----------------------+
| 1 | SIMPLE | user_account | range | user_account | user_account | 100 | NULL | 300021 | Using index condition |
+----+-------------+--------------+-------+---------------+---------------+---------+------+--------+-----------------------+
The rows value in the explain results has significantly decreased (this value is just an estimate, not the actual query result count), indicating that MySQL no longer needs to find all rows before extracting the required number of records.
This concludes a more efficient way to handle MySQL pagination. For more detailed information, you might want to check this presentation Efficient Pagination Using MySQL.