Pagination is an essential method to efficiently manage and display large data sets by dividing them into pages. It reduces server load and enhances user experience. This article covers three pagination methods: offset pagination, cursor pagination, and keyset pagination. Each of these pagination techniques—offset, cursor, and keyset—offers different advantages depending on the size of the data set and the application’s needs
Basic Concepts
- Page: A subset of the entire data set.
- Limit: The number of items displayed per page.
- Offset: The starting position for data retrieval on each page.
- Cursor: A pointer to a specific item, marking the beginning of the next page.
1. Offset-Based Pagination
Offset-based pagination uses two parameters: the number of items per page (limit) and the starting point (offset).
Code Example
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$limit = 10;
$offset = ($page - 1) * $limit;
// SQL query with limit and offset
$query = "SELECT * FROM products LIMIT $limit OFFSET $offset";
$result = $conn->query($query);
1st page
GET /api/products?limit=10&offset=0
2nd page
GET /api/products?limit=10&offset=10
3rd page
GET /api/products?limit=10&offset=20
Pros and Cons
- Pros: Simple and easy to implement.
- Cons: Can become inefficient on large data sets as offset becomes increasingly costly in performance.
2. Cursor-Based Pagination
Cursor-based pagination uses an ID or other key to mark the position of the last item on the current page, allowing retrieval from that point for the next page.
Code Example
// Retrieve the current cursor (ID of the last item on the previous page)
$last_id = isset($_GET['last_id']) ? (int)$_GET['last_id'] : 0;
$limit = 10;
// SQL query using the cursor
$query = "SELECT * FROM products WHERE id > $last_id ORDER BY id ASC LIMIT $limit";
$result = $conn->query($query);
// Display the results
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
$last_id = $row["id"];
}
1st page
GET /api/products?limit=10
2nd page
GET /api/products?limit=10&last_id=10
Pros and Cons
- Pros: Efficient for large data sets; faster than offset-based pagination.
- Cons: Complex to manage if the sorting order changes.
3. Keyset Pagination
Similar to cursor-based pagination in that it avoids using offset by starting from a specific position to load the next page. This approach uses an indexed column to retrieve data by setting a condition, such as ID > value, to limit results in an ordered sequence.
Pros and Cons
- Pros: Highly efficient for large data sets; ideal for indexed databases.
- Cons: Limited to data sets with indexed or sortable columns.
Conclusion
Each pagination method has its strengths and weaknesses, and the choice depends on your database structure and application needs.
