An efficient alternative to paging with SQL OFFSETs

Eoin:

Paging large MySQL tables can be slow using the typical offset method. This alternative method leveraging the primary key is a more efficient solution.

Originally posted on Developer Resources:

Challenge

Running WordPress.com means having multimillion-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, but inefficient, solution

The usual way of paging result sets in most SQL RDMS is to use the OFFSET option (or LIMIT [offset], [limit], which is the same).

1

But on a performance level, this means you’re asking your DB 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…

View original 240 more words

One thought on “An efficient alternative to paging with SQL OFFSETs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s