Doctrine paginator slow in Symfony

Speed up resource greedy pagination in Doctrine with this simple optimisation.

« Back to overview

The Doctrine Paginator class is by default configured in a way that may cause it to be very resource greedy, even with simple queries!

For instance, on an AWS Aurora mysql instance, we displayed a simple list of 25 items out of 8000.
The query contained no joins.
We experienced query times of over 60 seconds, causing gateway timeouts!

We only experienced this in the production database, (probably because the table is written to concurrently, so no mysql query caching was effective).

The problem is in the 'count' phase of the pagination. Counting number of results in an ORM is not an easy task - associations may be eager loaded, and the number of results returned by a query may not reflect the actual number of results as intended. This is presumably why the Paginator uses a resource intensive approach for counting results.

The paginator can however be easily configured to consume less resources and the speed up can be intense. Especially for simpler queries the following configuration of the paginator may fit the use case perfectly:

use Doctrine\ORM\Tools\Pagination\CountWalker;
use Doctrine\ORM\Tools\Pagination\Paginator;

// some query from the entity manager to be paginated

$query = $qb->getQuery();

// add hint for potentially more performance

$query->setHint(CountWalker::HINT_DISTINCT, false);

// create and configure the paginator
// the 'false' argument prevents 'fetching a join collection',
// setting 'use outputwalkers' accomplished most effect

$paginator = new Paginator($query, false);
$paginator->setUseOutputWalkers(false);

What happens with the queries executed by the Doctrine Paginator?

The offending 'count' query after the optimisations above differs from the original query, in a way that is much more efficient!

BEFORE:
Not optimized at all, all columns are requested. A temporary table is created. And all we need is counting the primary key field uid..

SELECT COUNT(*) AS dctrn_count FROM (SELECT c0_.uid AS api_uid_0, c0_.column1, c0_.column2, ............., c0_.columnN FROM mytable c0_ GROUP BY c0_.uid ORDER BY c0_.someorderingfield DESC) dctrn_table

AFTER:
This is way better.

SELECT count(c0_.uid) AS sclr_0 FROM mytable c0_ GROUP BY c0_.uid

So, if you suspect slow or db resource intensive queries that are unexpected, and Paginator is used, look for the following pattern in your (Symfony) query log:

SELECT COUNT(*) AS dctrn_count FROM (SELECT ... FROM mytable c0_ ....) dctrn_table

and try the optimisations above.

Doctrine paginator slow in Symfony

What happens with the queries executed by the Doctrine Paginator?

Also see: