Web Site Database browsing in pages

On a web page, the displaying of large quantities of data takes a lot of time for the server to deliver. It is therefore usual for web masters to show the data in pages where at any one time there is a limited number of records shown.

SQL has such a thing called LIMIT or ROWNUM. It basically limits the number of records retrieved from the database. Obviously to get the next page you need to know what is the last record retrieved. This means that you need to define OFFSET after the LIMIT.

This way of paging works quite well. The only problem is that every request means the database has to retrieve the SQL request again then go down to the OFFSET record then show the number of records set by LIMIT. If you have millions of records retrieved in this way then it is obviously process wasting.

There are some who advocate using a sequential incremented unique field as the offset instead. It will be something like this

SELECT * from table where uniquefield > xxx LIMIT 100

It has to remember the last record retrieved and replace the value xxx on the next page. However, this will work only for paging forward. Normally paging will allow forward, backward or start from a specific page. This can only be done with OFFSET.