Experiments

SQL_Pagination

2 min read

Pagination

  • SQL syntax
    • OFFSET/LIMIT (MySQL)
    • OFFSET/FETCH (MSSQL)
  • The syntax varies slightly by different databases. LIMIT is not supported by MSSQL and FETCH is not supported by MySQL
  • The following will return 3rd page, with pageSize=10 and pageNumber=2 (0-indexed)
  • MSSQL
SELECT *
FROM TableName
ORDER BY id
OFFSET 20 ROWS -- skip [pageNumber * pageSize] results
FETCH NEXT 10 ROWS ONLY; -- pageSize
  • MySQL
SELECT *
FROM TableName
ORDER BY id
LIMIT 10 -- pageSize
OFFSET 20; -- skip [pageNumber * pageSize] results

How total rows are calculated?

  • This does not seem like agreed way to do it consistently in all DB
  • In spring-data-jpa, Page response has information about the count but that comes at a cost of another query being fired internally, something like
select count(*)
from TableName ...
  • Hence getting count is not simple task

Bootstrap data for testing purposes

-- create
CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);
 
-- insert
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (0001, 'Clark', 'Sales');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (0002, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (0003, 'Ava', 'Sales');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (0004, 'Lua', 'Moon');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (0005, 'Charles', 'Smith');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (0006, 'Mindy', 'Stark');
 
-- fetch
-- your query here