0

Pagination — LIMIT/OFFSET vs cursor-based

Advanced5 min read·fw-07-006
sqlperformance

Concept

Pagination divides large result sets into pages. The two main strategies are offset-based (LIMIT/OFFSET) and cursor-based. Each has different performance characteristics and use cases.

Offset pagination (LIMIT 20 OFFSET 40):

  • Easy to implement. Supports random page access (page=5, page=10).
  • Performance degrades on high offsets — the DB must scan all preceding rows even if it only returns 20. OFFSET 10000 is slow.
  • Records can shift between requests (new inserts push rows to the next page).
  • Returns total count (for "Page 2 of 15" UI). Total count query: SELECT COUNT(*) FROM ... with same WHERE clause but no LIMIT.

Cursor pagination:

  • Uses WHERE id > ? instead of OFFSET. The DB uses the index — always fast.
  • No total count — no "Page 2 of 15" UI.
  • Only supports "next" and "previous" — no random page access.
  • Stable — new inserts don't shift existing records.
  • Best for infinite scroll, real-time feeds.

Paginator object: Wraps the results + pagination metadata:

  • Total count (offset only), current page, per-page, total pages.
  • previousPageUrl(), nextPageUrl().
  • links() for HTML page link generation.

hasMorePages(): Returns true if there's a next page. For cursor pagination, fetch perPage + 1 rows — if you get 21 rows when asking for 20, there's a next page.

Code Example

php
<?php
namespace Framework\Database;

class Paginator
{
    public function __construct(
        private readonly array $items,
        private readonly int   $total,
        private readonly int   $perPage,
        private readonly int   $currentPage,
    ) {}

    public function items(): array         { return $this->items; }
    public function total(): int           { return $this->total; }
    public function perPage(): int         { return $this->perPage; }
    public function currentPage(): int     { return $this->currentPage; }
    public function lastPage(): int        { return (int) ceil($this->total / $this->perPage); }
    public function hasMorePages(): bool   { return $this->currentPage < $this->lastPage(); }
    public function nextPage(): ?int       { return $this->hasMorePages() ? $this->currentPage + 1 : null; }
    public function previousPage(): ?int   { return $this->currentPage > 1 ? $this->currentPage - 1 : null; }
    public function firstItem(): int       { return ($this->currentPage - 1) * $this->perPage + 1; }
    public function lastItem(): int        { return min($this->firstItem() + count($this->items) - 1, $this->total); }

    public function toArray(): array
    {
        return [
            'data'          => $this->items,
            'current_page'  => $this->currentPage,
            'per_page'      => $this->perPage,
            'total'         => $this->total,
            'last_page'     => $this->lastPage(),
            'from'          => $this->firstItem(),
            'to'            => $this->lastItem(),
            'has_more'      => $this->hasMorePages(),
        ];
    }
}

class QueryBuilder
{
    public function paginate(int $perPage = 15, int $currentPage = 1): Paginator
    {
        // Count query
        $countBuilder = clone $this;
        $total = (int) $countBuilder->count();

        // Data query
        $offset = ($currentPage - 1) * $perPage;
        $items  = $this->limit($perPage)->offset($offset)->get();

        return new Paginator($items, $total, $perPage, $currentPage);
    }

    public function cursorPaginate(int $perPage = 15, ?int $afterId = null): array
    {
        $query = $this->limit($perPage + 1); // fetch one extra to detect next page
        if ($afterId !== null) {
            $query->where('id', '>', $afterId);
        }
        $items = $query->orderBy('id')->get();

        $hasMore  = count($items) > $perPage;
        $data     = $hasMore ? array_slice($items, 0, $perPage) : $items;
        $nextId   = $hasMore ? end($data)['id'] : null;

        return ['data' => $data, 'next_cursor' => $nextId, 'has_more' => $hasMore];
    }

    public function limit(int $limit): static { $this->limit = $limit; return $this; }
    public function offset(int $offset): static { $this->offset = $offset; return $this; }
}
PreviousINSERT, UPDATE, DELETE buildersQuery Builder from ScratchNextTesting the query builder — assert generated SQL stringsQuery Builder from Scratch