Zend Db limit() and offset() Pagination: Build, Verify, and Recover

What you are trying to do

You need to show page 201 of an events table without loading every row into PHP. In Zend Framework 2 and its Laminas successor, the Select object exposes limit() and offset() for exactly this. The database returns only the rows for the current page; PHP never sees the rest. That is the intended use, and it works well until the offset gets large or the query plan stops using an index.

Prerequisites and version assumptions

• PHP 7.4 or later with the PDO MySQL, mysqli, or PDO_PGSQL driver enabled.
• Zend Framework 2 (Zend\Db) or Laminas 2-3 (Laminas\Db). The Select::limit() and Select::offset() API is consistent across these versions; only the namespace changes.
• A table with a stable sort key. For the example, an events table with id and created_at, and an index on (created_at, id).
• Permission to run EXPLAIN (or EXPLAIN ANALYZE) on the target database. This is a read-only diagnostic and normally available to the application user.

Build the paginated Select

Create the Select with the columns you need, a deterministic order, and the page window. Ordering by a single non-unique column such as created_at can duplicate or skip rows between pages, so add the primary key as a tiebreaker.

use Laminas\Db\Adapter\Adapter;
use Laminas\Db\Sql\Select;
use Laminas\Db\Sql\Sql;

$page     = max(1, (int) ($_GET['page'] ?? 1));
$pageSize = 25;
$offset   = ($page - 1) * $pageSize;

$select = new Select('events');
$select->columns(['id', 'created_at', 'title'])
       ->order(['created_at DESC', 'id DESC'])
       ->limit($pageSize)
       ->offset($offset);

$sql       = new Sql($adapter);
$statement = $sql->prepareStatementForSqlObject($select);
$rows      = $statement->execute();

For ZF2, replace the Laminas namespace with Zend. The $adapter is a configured Adapter instance; in a ZF2/Laminas MVC application it is usually retrieved from the service manager.

Handle limit and offset safely

The Select object quotes integer limit and offset values for the target platform, so passing them through the object is safer than concatenating them into a SQL string. If you must build raw SQL for a driver-specific reason, cast both values to integers and bind any other user input through the adapter:

$pageSize = (int) $pageSize;
$offset   = (int) $offset;

$sqlString = 'SELECT id, created_at, title FROM events
              ORDER BY created_at DESC, id DESC
              LIMIT ? OFFSET ?';
$rows = $adapter->query($sqlString, [$pageSize, $offset]);

Some MySQL configurations with emulated prepares reject placeholders in LIMIT and OFFSET. Casting to int first removes the injection risk; if the driver still refuses, use the Select object path instead of raw SQL.

Check the query plan before trusting the page

Run the equivalent SQL through EXPLAIN in a database client. For MySQL 8.0 and PostgreSQL 12+, EXPLAIN ANALYZE executes the query and reports actual timing. Run this on a representative dataset, not an empty development table.

EXPLAIN ANALYZE
SELECT id, created_at, title
FROM events
ORDER BY created_at DESC, id DESC
LIMIT 25 OFFSET 5000;

What to look for: an index scan or index-only scan on (created_at, id), not a sequential scan followed by a sort. MySQL may still walk and discard the first 5000 index entries; that is expected with OFFSET. If the plan shows a full table scan or a filesort, the index is missing or not usable for this order. Adding the composite index is the usual fix:

CREATE INDEX idx_events_created_id ON events (created_at, id);

This changes schema state. To roll it back, run DROP INDEX idx_events_created_id ON events; (MySQL) or DROP INDEX idx_events_created_id; (PostgreSQL). Confirm the index name and syntax for your engine before running it in production.

Validate page boundaries and counts

Two checks catch most pagination bugs:

1. Boundary check. Request a page beyond the last one. The query should return an empty result set, not an error. If your code assumes at least one row, guard against an empty array before rendering.
2. Count consistency. Run a separate SELECT COUNT(*) with the same WHERE clause as the data query. SQL_CALC_FOUND_ROWS is deprecated in MySQL 8.0.17 and later, so a second count query is the portable choice. If the count and the page contents disagree, compare the two WHERE clauses first.

SELECT COUNT(*) FROM events;
-- Compare with the number of rows returned across all pages.

You can also confirm the driver and PHP versions match the expected configuration by checking phpinfo() for the PDO section, or running php -m | grep -i pdo from the command line. MySQLnd and the older MySQL driver can behave differently around prepared statement placeholders.

Limitations: when offset pagination stops being practical

OFFSET requires the database to locate and discard the skipped rows. Even with a good index, page 10,000 is slower than page 1 because the work grows with the offset. The research note flags this as version-sensitive beyond roughly 10,000 rows; treat that as a threshold to measure, not a fixed rule.

For deep pages, switch to seek-keyed (keyset) pagination. Instead of an offset, pass the last row's sort values:

SELECT id, created_at, title
FROM events
WHERE (created_at, id) < (:lastCreatedAt, :lastId)
ORDER BY created_at DESC, id DESC
LIMIT 25;

This uses the index directly and does not grow with page depth. The trade-off is that you cannot jump to an arbitrary page number without a separate lookup, and the sort key must be unique and stable.

Recovery options

• Slow page: check EXPLAIN for a sequential scan or filesort, then add or adjust the composite index. Roll back with DROP INDEX if the index causes write overhead you cannot accept.
• Wrong or duplicated rows: verify the ORDER BY includes a unique tiebreaker and that the count query uses the same WHERE clause.
• Driver rejects placeholders: switch from raw SQL to the Select object, or cast the values to integers and interpolate them after validation.
• Deep offsets are required: move to seek-keyed pagination, or cache page results if the data changes infrequently.

Practical check

After the change, run one page request against a copy of production data, capture the EXPLAIN plan, and confirm the row count for the page matches the expected page size. If the plan shows an index scan and the boundary page returns an empty set without error, the pagination is behaving as intended. Re-run the check after any index or schema change, because PostgreSQL and MySQL can choose different plans as statistics change.