Faster Pagination in Mysql — You Are Probably Doing It Wrong

Queries with LIMITs and OFFSETs are common in applications that require pagination and in some cases might work well for a while. In many cases though, they become slow and painful once the OFFSET has a high value.

Why OFFSET is so slow?

Well, in most cases, low offset queries are not slow. The problem starts with high OFFSET values.

If your query is using the following limit clause: “LIMIT 50000, 20”, it’s actually requesting the database to go through 50,020 rows and throw away the first 50,000. This action can have a high cost and impact on response time.

You may ask yourself “who the heck is going to skip to page 50,000 in my application?”.
Let’s list few possible use cases:

• Your favorite search engine (Google / Bing / Yahoo) is about to index your ecommerce website. You have about 100,000 pages on that website. How will your application react when the search bot will try to fetch those last 50,000 pages to index them? How frequently will that happen?
• In most web applications, we allow the user to skip to the last page, and not only the next page. What will happen when the user will try to skip to page 50,000 after visiting page 2?
• What happens if a user landed in page 20,000 from a Google search result, liked something there and posted it on facebook for another 1000 friends to read?

I have tested the following OFFSET values with the following query, to present the performance deterioration as the OFFSET grows. The query was executed on a table that holds products with 150,000 records. The data is real user information and not auto-generated.

How to optimize slow OFFSET queries?

To optimize slow OFFSET queries, you can either limit the number of permitted pages in a pagination view or simply just not use OFFSET.
A good alternative for using OFFSET will be the Seek Method.

In simple words, the seek method is all about finding a unique column or set of columns that identifies each row. Then, instead of using the OFFSET clause, we can just use that unique value as a bookmark that presents the position of the last row we’ve fetched and query the next set of rows by starting from this position in the WHERE clause.

For example, looking at the queries we executed before, assuming the last event id in offset 999,999 was ‘111866’, the query will be:

Here, you need to make sure to order by the unique columns, so that the order is always kept the same between pages, otherwise you might get unexpected behavior.

This is a comparison of the performance between both methods. The interesting observation here is not only that the performance of the Seek method is better, but that it’s also more stable no matter how far you paginate into the table.

Conclusion

I do not recommend using the OFFSET capability in MySQL to implement paging capabilities. When data grows, you’ll probably start noticing performance issues. Instead, consider using the Seek Method described above.