Stop Slicing Arrays: Paginate at the SQL Layer with Laminas Paginator's DbSelect Adapter

A common performance bug hides in plain sight in PHP apps: the controller fetches every row from a table, wraps it in a paginator, and slices the array down to 25 items for display. The page renders fine in development. Then the table grows to half a million rows and every listing request loads all of them into memory just to discard 99.995% of the work.

The fix is conceptually simple — push the pagination down into the query itself — and Laminas ships a ready-made way to do it: Laminas\Paginator\Adapter\DbSelect. Instead of paginating a result set, you paginate a query, and the adapter rewrites it with LIMIT/OFFSET per page plus a separate COUNT query for the total. This post walks through how it works, where it breaks down, and how to verify it's actually doing what you think.

What the DbSelect adapter actually does

The adapter wraps a Laminas\Db\Sql\Select object. When you ask the paginator for the items on a page, it clones your select, applies the platform-appropriate limit and offset through Laminas\Db's SQL abstraction (so it works across MySQL, PostgreSQL, SQL Server, etc.), and executes only that bounded query. The total item count comes from a second query derived from your select — essentially your query re-expressed as a COUNT.

Two consequences follow. First, memory usage per request becomes proportional to your page size, not your table size. Second, you issue two queries per page view instead of one — a worthwhile trade in almost every case, but worth knowing.

A worked example

Assume a laminas/laminas-db adapter in $dbAdapter and an orders table. This code runs in whatever layer builds your data for the request handler — a controller in laminas-mvc or middleware in Mezzio; the paginator component is deliberately decoupled from the MVC stack, so the pattern is identical in both.

use Laminas\Db\Sql\Sql;
use Laminas\Paginator\Adapter\DbSelect;
use Laminas\Paginator\Paginator;

$sql = new Sql($dbAdapter);
$select = $sql->select('orders')
    ->where(['status' => 'open'])
    ->order('created_at DESC');

$adapter = new DbSelect($select, $sql);

$paginator = new Paginator($adapter);
$paginator->setItemCountPerPage(25);
$paginator->setCurrentPageNumber($page); // validated user input

foreach ($paginator as $row) {
    // only the 25 rows for this page
}
$totalPages = $paginator->count();

If you want hydrated objects instead of arrays, pair the adapter with a Laminas\Db\ResultSet\HydratingResultSet (pass it as the third constructor argument to DbSelect); each page then hydrates directly into your entity class while keeping memory bounded by page size.

The $page value typically comes from a query parameter. Clamp it before use — cast to int, enforce a minimum of 1, and cap items-per-page if you expose that too. An unvalidated page number produces invalid offsets; an unvalidated page size lets a client ask for 100,000 rows per page, which defeats the entire exercise.

The COUNT query trap

The derived count query works well for straightforward selects. It goes wrong when your select uses GROUP BY, DISTINCT, or HAVING, or joins that fan out rows. In those cases the count can over- or under-report the real number of pages, and users see phantom empty pages or missing results.

The escape hatch is supplying your own count select. Build a second Select that counts exactly what you consider an "item" — for example, COUNT(DISTINCT orders.id) on a grouped join — and pass it to the adapter. If your listing query is anything beyond a flat filtered select, budget the time to check the count against reality.

Limitations worth knowing before you commit

Deep offsets degrade. OFFSET 500000 LIMIT 25 still makes the database scan and discard half a million rows. For very large or churn-heavy tables, keyset (seek) pagination — WHERE id < :last_seen_id ORDER BY id DESC LIMIT 25 — performs far better, at the cost of losing arbitrary page jumps. DbSelect is the right tool for typical admin listings and search results; it is not the right tool for an infinite-scroll feed over tens of millions of rows.

Package maintenance status. laminas-db has been the subject of long-running maintenance-mode discussions in the Laminas community, and component support status changes over time. Before adopting this for a new project, check the Laminas project's current status page and the laminas-db and laminas-paginator repositories for supported PHP versions and maintenance state. The code above reflects long-established behavior, but the strategic decision to build on it deserves a current check.

Verify it instead of trusting it

Don't assume the adapter is limiting queries — prove it. Create a test table with a few thousand rows, paginate at 25 per page, and enable your database's query log. You should see, per page request: one SELECT ... LIMIT 25 OFFSET n and one COUNT query, and nothing pulling full result sets. Then compare peak memory (memory_get_peak_usage() around the fetch) against your old fetch-everything-and-slice version on the same table. The difference should scale with table size; if it doesn't, something upstream is still materializing all rows.

The takeaway: if you're slicing arrays to paginate database results, you're paying full-table cost for page-sized output. DbSelect moves that boundary into SQL with about ten lines of code — just respect the COUNT edge cases, clamp user input, and confirm the maintenance status of the underlying packages first.