TRUE/REAL data pagination

I was recently implementing Lazy Loading in Primefaces

One of the insights i would like to share is that real efficient pagination is achieved by fetching data from the database (backend) in chunks as opposed to loading everything in memory.

This saves both bandwidth and memory (client side).

The trick here is to use OFFSET and LIMIT sql clauses in conjuction with ORDER BY.

Example 1: Returning the first 100 rows from a table called employee:

SELECT employee_id, employee_name, remarks FROM employee ORDER BY employee_id ASC LIMIT 100;

Example 2: Returning 10 rows from the table employee starting at row 15

SELECT employee_id, employee_name, remarks FROM employee ORDER BY employee_id ASC OFFSET 15 LIMIT 10;