# Pagination in MySQL Any good DBA will tell you to " [select only what you need.](https://planetscale.com/learn/courses/mysql-for-developers/queries/select-only-what-you-need)" It's one of the most common aphorisms, and for good reason! We don't ever want to select data that we're just going to throw away. One way this advice manifests itself is to not use `SELECT *` if you don't need all the columns. By limiting the columns returned, you're selecting only what you need. Pagination is another way to "select only what you need." Although, this time, we're limiting the _rows_ instead of the _columns_. Instead of pulling all the records out of the database, we only pull a single page that we're going to show to the user. There are two primary ways to paginate in MySQL: **offset/limit and cursors**. Which method you choose depends on your use case and your application's requirements. Neither is inherently better than the other. They each have their own strengths and weaknesses. ## [The importance of deterministic ordering](https://planetscale.com/blog/mysql-pagination#the-importance-of-deterministic-ordering) Before we talk about the wonders of pagination, we need to talk about [deterministic ordering](https://planetscale.com/learn/courses/mysql-for-developers/queries/sorting-and-limiting). When your query is ordered deterministically, it means that MySQL has enough information to order your rows in the exact same way every single time. If you sort your rows by a column that is not unique, MySQL gets to decide which order to return these rows in. Let's look at an example. Given this table full of people named Aaron: ``` | id | first_name | last_name | |----|------------|-----------| | 1 | Aaron | Francis | | 2 | Aaron | Smith | | 3 | Aaron | Jones | ``` Let's run a query to order those people by their first name: ``` SELECT * FROM people ORDER BY first_name ``` Because all three people have the same first name, MySQL gets to decide which order to return the rows in! Depending on certain factors, the order may change. This is because the ordering is not deterministic enough. The easiest way to produce deterministic ordering is to order by a unique column because every value will be distinct, and MySQL will have no choice but to return the rows in the same order every time. In most cases, simply adding the `id` is the best way to go. ``` SELECT * FROM people ORDER BY first_name, id -- Add ID to ensure deterministic ordering ``` Now MySQL knows that when given two `first_name` values that are the same, it should then look at the `id` column to determine the order. This is deterministic ordering, and it's a prerequisite to effective pagination. ## [Offset/limit pagination](https://planetscale.com/blog/mysql-pagination#offsetlimit-pagination) Offset/limit pagination is likely the most common way to paginate in MySQL because it's the easiest to implement. With offset/limit pagination, we're taking advantage of two SQL keywords: `OFFSET` and `LIMIT`. The `LIMIT` keyword tells MySQL how many rows to return, while `OFFSET` tells MySQL how many rows to skip over. ``` SELECT * FROM people ORDER BY first_name, id LIMIT 10 -- Only return 10 rows OFFSET 10 -- Skip the first 10 rows ``` In this example, we're selecting all the people from the `people` table, ordering them by `first_name` and `id`, and then limiting the result set to 10 rows. We're also skipping the first 10 rows, returning rows 11-20. ## [Strengths of offset/limit pagination](https://planetscale.com/blog/mysql-pagination#strengths-of-offsetlimit-pagination) One of the great strengths of offset/limit pagination is that it's easy to implement and easy to understand. It doesn't require tracking any state over time; each request can stand alone. Another strength of this method is that pages are directly addressable. Users who want to navigate from page 1 directly to page 10 can do so quite easily, provided your interface exposes page links. ## [Offset/limit pagination and drifting pages](https://planetscale.com/blog/mysql-pagination#offsetlimit-pagination-and-drifting-pages) One weakness of offset/limit pagination is that pages can drift. This is true of cursor-based pagination as well, but it's more likely to happen with offset/limit pagination. While your user is viewing the page, a person can be deleted, causing the pagination to skip records. This might be an acceptable tradeoff for you based on your application. ## [Performance drawbacks of offset/limit pagination](https://planetscale.com/blog/mysql-pagination#performance-drawbacks-of-offsetlimit-pagination) The way that the `OFFSET` keyword works is that it _discards_ the first `n` rows from the result set. This means that as you work into deeper and deeper pages, the performance of your query will degrade. Very deep pages can take multiple seconds to load. ## [Deferred joins for faster offset/limit pagination](https://planetscale.com/blog/mysql-pagination#deferred-joins-for-faster-offsetlimit-pagination) There is a technique known as a " [deferred join](https://planetscale.com/learn/courses/mysql-for-developers/examples/deferred-joins)" that can optimize offset/limit pagination. ``` SELECT * FROM people INNER JOIN ( -- Paginate the narrow subquery instead of the entire table SELECT id FROM people ORDER BY first_name, id LIMIT 10 OFFSET 450000 ) AS tmp USING (id) ORDER BY first_name, id ``` ## [Cursor pagination](https://planetscale.com/blog/mysql-pagination#cursor-pagination) Now that we're thoroughly versed on the offset/limit method let's talk about cursor-based pagination. Cursor-based pagination is a method that uses a "cursor" to determine the next page of results. The idea is that you have a cursor that points to the last record that the user saw. ## [Drawbacks to cursor-based pagination](https://planetscale.com/blog/mysql-pagination#drawbacks-to-cursor-based-pagination) Cursor-based pagination is more complicated to implement than offset-based pagination. Constructing the cursor and the `WHERE` clause requires more thought. You also have to keep track of that little piece of state: the cursor. ## [Benefits of cursor-based pagination](https://planetscale.com/blog/mysql-pagination#benefits-of-cursor-based-pagination) One of the advantages of cursor-based pagination is its resilience to shifting rows. For example, even if the cursor points to a record that was deleted, the database knows to locate the next record based on the cursor. ### [Cursor based pagination performance](https://planetscale.com/blog/mysql-pagination#cursor-based-pagination-performance) Cursor-based pagination can be much more performant than offset/limit simply because it accesses much less data. Ensure a proper [indexing strategy](https://planetscale.com/learn/courses/mysql-for-developers/indexes/introduction-to-indexes) to allow the database to efficiently find records. ## [Conclusion](https://planetscale.com/blog/mysql-pagination#conclusion) Pagination is a common requirement for almost every web application or API. Now you understand the different types of pagination and the tradeoffs that come with each.