We know the pagination becomes very slow if we use
offset based pagination alone, as time progress and the DB gets accumulated with a huge volume of records.
This post is intended to give some hints on improving pagination performance.
Assume we have
posts table with following fields
Assume we have 1M records in the posts table.
mysql> select * from posts order by created_at desc, title asc limit 20 offset 0;
The query takes 0.08 secs.
mysql> select * from posts order by created_at desc, title asc limit 20 offset 50000;
The query takes 1.17 secs.
mysql>select * from posts order by created_at desc, title asc limit 20 offset 75000;
The query now takes 2.60 secs. So the time gets increases when ever the offset value increases.
To handle this one, we shall filter the records using
created_at field and indexing the
Step: 1 fetch the latest created record date
mysql> select created_at from posts order by id desc limit 1
Assume we get the result as
Now we shall use this
created_at for pagination.
Fetching first page.
mysql> select * from posts where created_at <= '2020-05-01 13:00:40.949669' order by created_at desc, title asc limit 20
The query takes just 0.07 seconds.
Assume the 20th record of the result as follows
117414 | Money saving schemes | Personal Finance | 2020-05-01 13:00:29.981149 | 2020-06-17 13:00:29.981493
Now to fetch the second page, we need to filter the records using the above
created_at value. i.e
Now the query will be
mysql> select * from posts where created_at <= '2020-05-01 13:00:29.981149' order by created_at desc, title asc limit 20
The query takes same 0.07 seconds.
The main advantage with this sort of pagination takes almost the consistent time i.e 0.07 secs (here) whenever we fetch any page. This type of implementation is well suited for