Faster Pagination in Mysql — You Are Probably Doing It Wrong

Why OFFSET is so slow?

  • 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?

How to optimize slow OFFSET queries?

Conclusion

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Ramzi Alqrainy

Ramzi Alqrainy

Apache Solr Contributor | Slack Contributor | Speaker | Chief Technology Officer at The Chefz| Technical Reviewer for Big Data Books