Implementing pagination in MySQL

As a web developer, you have likely implemented pagination numerous times. Pagination is necessary when you have a vast amount of records to show, but you do not want to overwhelm the user with all the data at once. Instead, you break the records into smaller, more manageable chunks called "pages." In this video, we will examine one of two methods used for pagination: limit/offset pagination.

Deterministic ordering is key

Before diving into the different methods of pagination, it is important to emphasize the significance of ensuring deterministic ordering. If your ordering is not deterministic, your records may not show up correctly, which can lead to confusion for your users. For pagination to be effective, you must order your records in a stable and deterministic manner.

The limit offset method

Let's start by examining the limit offset method. With this method, the first step is to select records from the database. You should then order the records by a field (or multiple fields!) to ensure deterministic ordering. The next step is to specify the page size by using the LIMIT keyword followed by the number of records you want to show per page. Finally, you specify the offset by using the OFFSET keyword followed by the starting position for the current page.

Here's an example:

SELECT
  *
FROM
  people
ORDER BY
  birthday,
  id
LIMIT 100
OFFSET 0;

In this example, we are selecting all records from the people table, ordering by the birthday column, and showing 100 records per page. The OFFSET is set to 0 because we are on the first page.

Strengths of limit/offset method

One significant advantage of the limit offset method is that it is user-friendly and easy to implement. You can create a simple query to construct the pagination, and users can jump directly to the page they want to view by using their desired offset number. This method provides directly addressable pages, so if users want to jump to a certain page, they can do so quickly and easily.

Drawbacks of limit/offset method

One significant limitation of limit offset is that the page numbers can drift as you navigate through the records. For example, if a record is deleted from your current page, it may cause a record to shift from the next page to the current page, leading to confusion. Moreover, as you navigate deeper into the records, the method becomes significantly more expensive, meaning the database has to do more work to fetch that specific page.

Conclusion

Limit/offset is easy to use and ideal for directly addressable pages, but it is less well suited for records that frequently shift around.

No matter which method you choose, deterministic ordering for your records is critical, and it ensures that your users receive consistent, accurate data.