In this article, I will go over a performant technique for paginating records.
The most common way of implementing pagination is by using offset based pagination. But this is not really performant technique when dealing with huge dataset.
Let's say we need to write a query to fetch latest posts for a social media website.
SQL query would look like this:
SELECT * FROM POSTS ORDER BY ID DESC OFFSET 0 LIMIT 10
Let's say you want to select next page for page 2 you will change offset to 10 & for page 3 offset will be 20 as mentioned below:
SELECT * FROM POSTS ORDER BY ID DESC OFFSET 10 LIMIT 20
SELECT * FROM POSTS ORDER BY ID DESC OFFSET 20 LIMIT 30
This technique selects all the records then skips records. And for that reason it is not really a performant technique. Consider a scenario where you are skipping million rows in order to fetch the next 10 records.
But there is a way, we can improve on this:
SELECT * FROM WHERE POSTS ID < {MAX_POST_ID+1} ORDER BY ID DESC LIMIT 10
This query would only select all the records that need to be returned & hence this would be much faster. Also since ID is an index so search over it would be much faster.
For first page, data returned would look like this.
ID BODY
20 Post Body
19 Post Body
18 Post Body
17 Post Body
16 Post Body
15 Post Body
14 Post Body
13 Post Body
12 Post Body
11 Post Body
And to fetch next 10 records(and subsequent queries) you would pass 11 as post_id. So query would look like this:
SELECT * FROM POSTS
WHERE ID < {POST_ID}
ORDER BY ID DESC LIMIT 10
ID BODY
10 Post Body
09 Post Body
08 Post Body
07 Post Body
06 Post Body
05 Post Body
04 Post Body
03 Post Body
02 Post Body
01 Post Body
So basically to fetch next page all you have to do is to pass post_id of last record returned from previous query.
This kind of pagination is not length aware. Cause again if we go on to calculate length then it would slow down the API call & subsequently apps will take longer to load newsfeed.
Top comments (3)
Thanks for sharing, but what would be the approach when the
IDcolumn is of typestring?Hi Andrei, first off, thanks for reading the article. If you are concerned that DBMS might order by
IDin a different way than what you expect.In that case you can always go for custom collation. dev.mysql.com/doc/refman/5.6/en/ad....
A collation basically is set of rules used by DBMS to compare & sort strings. It is much like a comparator function you are used to in JS.
This way you can make your comparisons predictable & inline with your business needs.
Thank you very much!