Page 1 of your product list loads in 5 ms. Page 5,000 takes 4 seconds. The query is the same except for one number:
SELECT id, name, price
FROM products
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;
This is how OFFSET works in every major database, not a MySQL bug. The fix is to change how you paginate.
Why OFFSET gets slower with every page
A database can't jump to "the 100,001st row". A B-tree index is sorted, but it doesn't record each row's position. To serve OFFSET 100000, the engine walks the index from the start, reads 100,020 rows, throws away the first 100,000, and returns 20.
The cost grows in a straight line with the page number. Page n costs O(n). A crawler or a user clicking "last page" forces a near-full scan on every request. In MySQL it's worse when the index doesn't cover the query, because InnoDB fetches the full row for each discarded entry too.
OFFSET also has a correctness problem: if a row is inserted or deleted while someone is paging, everything shifts by one. Users see duplicates or miss rows between pages.
Keyset pagination (the "seek method")
Instead of "skip N rows", ask for "the rows after the last one I saw". Remember the sort key of the last row on the page and continue from there:
-- First page
SELECT id, name, price, created_at
FROM products
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Next page: pass the last row's (created_at, id)
SELECT id, name, price, created_at
products
(created_at, id) (, )
created_at , id
LIMIT ;
