Pagination with offset

From SocialStrap Wiki
Jump to: navigation, search

Challenge

Running a busy social network means having million-record database tables. Tables which we often need to batch-query.

Provided we could hardly select (or update, etc) millions of records at once and expect speed, we commonly have to "page" our scripts to only handle a limited number of records at once, then move on to the next batch.


Classic and inefficient solution

The usual way of paging result sets in most SQL databases is to use the LIMIT [offset], [limit].

SELECT * FROM my_table OFFSET 8000000 LIMIT 100;

But on a performance level, this means you're asking your database engine to figure out where to start from all on its own, every time. Which then means it must be aware of every record before the queried offset, because they could be different between queries (deletes, etc). So the higher your offset number, the longer the overall query will take.


Alternative solution

Instead, of keeping track of an offset in your query script, consider keeping track of the last record's primary key in the previous result set instead. Say, its ID. At the next loop instance, query your table based on other records having a greater value for said ID.

SELECT * FROM my_table WHERE id > 7999999 LIMIT 100;

This will let you page in the same way, but your database engine will know exactly where to start, based on an efficient indexed key, and won't have to consider any of the records prior to your range. Which will all translate to speedy queries.

Here's a real-life sample of how much difference this can make:

mysql> SELECT * FROM posts LIMIT 8000000, 10;
10 rows in set (12.80 sec)
 
mysql> SELECT * FROM posts WHERE id > 12958559 LIMIT 10;
10 rows in set (0.01 sec)

I received the very same records back, but the first query took 12.80 seconds, while the alternative took 0.01 instead.


SocialStrap's feed offset explained by example

first page https://live.socialstrap.net/alcalbg feed_offset=6030

next page = https://live.socialstrap.net/alcalbg/?feed_offset=6030 feed_offset=5872

next page = https://live.socialstrap.net/alcalbg/?feed_offset=5872 and so on...