Queries with LIMITs and OFFSETs are common in application that
require pagination and in some cases might work well for a
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 an impact 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 / DuckDuckGo / whatever) is about to index your ecommerce website. You have about 100,000 pages in that website. How will your application react when the …