Doctrine DQL Pagination: When OFFSET Slows Down and What to Use Instead

The problem: page 50 is slow, but the query looks simple

You open an admin panel, click to page 50 of a user list, and the request hangs for several seconds. The DQL query itself is straightforward, but Doctrine’s default pagination is scanning and discarding thousands of rows before it returns the twenty you asked for.

Doctrine’s setFirstResult() and setMaxResults() methods map to SQL OFFSET and LIMIT. They are fine for the first few pages, but they degrade as the offset grows. For deep pagination, keyset pagination is usually the better choice.

How Doctrine OFFSET pagination works

Run this inside a controller action, repository method, or CLI command during a normal request. The database user only needs SELECT permission.

$qb = $entityManager->createQueryBuilder();
$qb->select('u')
   ->from(User::class, 'u')
   ->orderBy('u.id', 'ASC');

$query = $qb->getQuery();
$query->setFirstResult(1000);
$query->setMaxResults(20);

$users = $query->getResult();

Expected check: count($users) should be 20, or fewer if fewer than 1,020 rows exist. Risk: the database still reads and discards the first 1,000 matching rows before returning the page, and Doctrine hydrates each result into the Unit of Work, adding memory and change-tracking overhead even for read-only lists.

Why large offsets get expensive

An OFFSET 10000 LIMIT 20 query does not start at row 10,001 for free. The database typically walks through the index or table, skips 10,000 rows, then returns 20. As the offset grows, the skipped work grows with it. If you wrap the query in Doctrine’s Paginator, Doctrine also runs a separate COUNT(*) query to calculate total pages, which can be slow on large tables.

Style	Cost pattern	Best for	Main limitation
OFFSET / LIMIT	Grows as offset increases	First few pages, arbitrary page numbers	Slow deep pagination
Keyset (seek)	Stable per page	Infinite scroll, activity feeds	No direct “go to page 42”; needs a stable sort column

Keyset pagination example

Keyset pagination, sometimes called seek pagination, replaces the offset with a filter on the last ID you returned. This works well for infinite scroll or activity logs where users rarely need arbitrary page numbers.

Run this in an API endpoint that returns the next batch of results. Permissions remain SELECT only.

$query = $entityManager->createQuery(
    'SELECT u FROM App\\Entity\\User u'
    . ' WHERE u.id > :lastId'
    . ' ORDER BY u.id ASC'
);
$query->setParameter('lastId', $lastSeenId);
$query->setMaxResults(20);

$users = $query->getResult();

Expected check: the response contains at most 20 entities, and every ID is greater than the previous $lastSeenId. Risk: if your sort column is not unique, add a tie-breaker such as the primary key so rows are not skipped or duplicated when values are equal.

How to choose and verify

Use OFFSET pagination when the offset stays small—roughly under a few thousand rows—and users need numbered pages. Switch to keyset pagination when users page deeply or when you implement “load more” behavior.

To verify which one you are actually running, enable the Doctrine SQL logger or your database’s slow-query log, then run EXPLAIN ANALYZE against the generated SQL. Look for the number of rows scanned versus rows returned. If scanned rows climb with the page number, you have found the pagination cliff.

Limitation

Keyset pagination does not support jumping directly to page 42 without tracking cursors for every page boundary. It is also less useful when the sort order can change between requests, because a deleted or updated row can shift the visible window.

What to check next

Open your slowest list view and open page 20 or 50. If the response time jumps compared to page 1, replace setFirstResult with a keyset filter for that endpoint. Monitor the query log after the change to confirm scanned rows no longer grow with the page number.