You Are Doing SQL Pagination Wrong

Almost all applications using SQL fall into this common performance trap. Avoid it now!

Every application faces these issues in its early stages: you have to limit the quantity of data sent to a client for its requests to prevent your quality of service to degrade. We find this pattern everywhere:

The delicate part becomes “how we implement this solution?”.

We are going to look here at a simple use case, for pagination with a table object of 1000000 rows. We are considerating 2 different pagination cases: one following the unique primary key id and the other one by tenant name (varchar field with an index).

objects table description — using mariadb 10.5.8

Naive implementation

The easiest solution for pagination, found everywhere on the internet is the usage of LIMIT and OFFSET.

Let’s try this query for the 2 indexed columns (id and tenant), for different values of OFFSET:

Pagination response time for different OFFSET values

When we inspect the database optimizer query plan for our pagination query: we notice that the database is using a filesort operation for ordering on the non-indexed column and an index for the tenant and id column. Based on that we can conclude that:

1. Using offset on a non-indexed sorted column is a heavy task for the database and must be avoided at all costs.

MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause.

2. Using OFFSET, even on the primary key, becomes slower over time for high database volume, as the number of rows that need to be loaded in order to be skipped becomes higher and higher.

3. Using offset on an indexed field with a big key_length is slower than on a small key_length.

As we can see, OFFSET pagination has some drawbacks:

• For a high database volume, the end pages are harder to retrieve than the…