Using created_at field to improve pagination performance

Introduction

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
id
title
description
created_at
updated_at

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 created_at field.

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 2020-05-01 13:00:40.949669

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 2020-05-01 13:00:29.981149

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 endless pagination.