Paginating Large Result Sets with Zend\Db\Sql\Select and Zend\Paginator

Problem: Loading whole tables kills performance

When a controller action fetches every row from a table and passes the full result set to a view, memory usage spikes and response time grows linearly with table size. For tables with hundreds of thousands of rows this can cause noticeable latency or even out‑of‑memory errors.

Thesis: Declarative pagination limits rows fetched

By building a Zend\Db\Sql\Select object with LIMIT and OFFSET clauses and wrapping it in a Zend\Paginator\Adapter\DbSelect, the database returns only the rows needed for the current page. Zend\Paginator then handles page navigation, total‑page calculation, and view integration.

Worked example: Controller action

The following snippet shows a typical action in a Laminas/MVC controller. Replace placeholders with your actual table name, database adapter, and view model.

use Zend\Db\Sql\Select;
use Zend\Paginator\Adapter\DbSelect;
use Zend\Paginator\Paginator;

public function usersAction()
{
    // 1. Get requested page (default to 1) and page size
    $page   = (int)$this->params()->fromQuery('page', 1);
    if ($page < 1) { $page = 1; }
    $pageSize = 10; // you could make this configurable

    // 2. Build the SELECT with limit/offset
    $select = new Select('users');
    $select->limit($pageSize)
           ->offset(($page - 1) * $pageSize)
           ->order(['id ASC']); // deterministic order required for stable pagination

    // 3. Create the DbSelect adapter
    $adapter = new DbSelect($select, $this->getServiceLocator()->get('Zend\Db\Adapter\Adapter'));

    // 4. Instantiate the paginator
    $paginator = new Paginator($adapter);
    $paginator->setCurrentPageNumber($page)
              ->setItemCountPerPage($pageSize);

    // 5. Pass paginator to the view
    return new ViewModel(['paginator' => $paginator]);
}

Trade‑off: Extra COUNT query

The DbSelect adapter runs a second query (SELECT COUNT(*) FROM users) to determine the total number of rows, which enables the paginator to compute the total page count. This adds overhead, especially on very large tables or when the count is expensive.

Limitations to keep in mind:

• The database user must have permission to execute COUNT on the target table; otherwise the paginator throws an exception.
• When ordering by non‑unique columns, add a deterministic tie‑breaker (e.g., the primary key) to avoid shifting page boundaries between requests.
• For real‑time feeds or tables where the count changes frequently, consider keyset pagination (seek method) or caching the count result.

Verification steps

1. Enable the Zend\Db\Profiler in your configuration and run the action with different page values.
2. Check the profiler log: you should see one query with LIMIT/OFFSET and a separate SELECT COUNT(*) query.
3. Confirm that the rendered view contains exactly $pageSize items (or fewer on the last page).
4. Click the pagination links and verify that the URL reflects the correct page number and that the displayed items change accordingly.
5. Change $pageSize or add rows to the table and ensure the paginator’s page count updates without manual code changes.

Actionable closing

Start by refactoring any controller that loads entire tables into the pattern shown above. Measure the impact with your profiler and monitor the extra COUNT query’s cost. If the COUNT becomes a bottleneck, evaluate caching strategies or migrate to keyset pagination for immutable or append‑only datasets.