Pagerfanta and Doctrine2 COUNT optimization

Question

I'm using Pagerfanta and Doctrine Adapters with Symfony2 and Silex. As my database became bigger I noticed huge load on admin stats pages that display big data with pagination. I checked profiler and saw unbelievably inefficient queries:

SELECT DISTINCT id16
FROM (
    SELECT f0_.username AS username0, ..., f0_.added_on AS added_on20
    FROM fos_user f0_ ORDER BY f0_.id DESC
) dctrn_result
LIMIT 50 OFFSET 0;

SELECT COUNT(*) AS dctrn_count
FROM (
    SELECT f0_.username AS username0, ..., f0_.added_on AS added_on20
    FROM fos_user f0_ ORDER BY f0_.id DESC
) dctrn_result
LIMIT 50 OFFSET 0;`

First query was easy to fix by creating fixed version of DoctrineORMAdapter class. The code that generates COUNT() query is more complicated so I decided to ask if there's any solution for this.

So is there any way to make Pagerfanta not running nested queries?

If you see the code generating an unnecessary complicated query, maybe you should write a bug report at the project's site. — lxg, Aug 10 '14 at 20:28

score 9 · Accepted Answer · answered Jul 01 '15 at 10:59

Better late than never: I've hit the same wall today with >200k records and found a solution.

Pagerfanta internally uses Doctrine\ORM\Tools\Pagination\CountOutputWalker to count objects which results in a count query like this:

SELECT 
  COUNT(*) AS dctrn_count 
FROM 
  (
    SELECT 
      DISTINCT id_0 
    FROM 
      (
        SELECT 
          m0_.id AS id_0, 
          ...
        FROM 
          messaging_messages m0_ 
        ORDER BY 
          m0_.id DESC
      ) dctrn_result
  ) dctrn_table

To bypass CountOutputWalker we can pass a flag when instantiating DoctrineORMAdapter. So instead of simply

$adapter = new DoctrineORMAdapter($qb);

you do

$adapter = new DoctrineORMAdapter($qb, true, false);

(third parameter). This turns the count query into a much more efficient one:

SELECT 
  count(DISTINCT m0_.id) AS sclr_0 
FROM 
  messaging_messages m0_

You have to update whiteoctober/Pagerfanta to 1.0.3 though.

Issue

Related commit

Note that this will not work if your SELECT query has a HAVING clause — LovingGarlic, Aug 12 '21 at 02:38

score 0 · Answer 2 · answered Feb 17 '15 at 12:40

In your case it's not pagerfanta that does the sub-queries. It's the source your query builder instance it's coming from.

I usually have a function in entity repository that returns a plain query builder instance instead of the results. It's left to you to write an efficient query builder. Then I feed that query builder into DoctrineORMAdapter.

I've got this helper function that I use throughout my projects:

/**
 * Pass an array, entity or a custom QueryBuilder instance to paginate.
 * Takes an array of parameters as a second argument.
 * Default parameter values:
 *
 * $params = array(
 *     'curPage' => 1,
 *     'perPage' => 15,
 *     'order' => 'DESC'
 * );
 *
 * @param mixed $object
 * @param array $params
 *
 * @return Pagerfanta
 */
public function paginate($object, $params = array())
{
    if (is_array($object)) {
        $adapter = new ArrayAdapter($object);
    } elseif ($this->isEntity($object)) {
        $qb      = $this->em->createQueryBuilder()
            ->select('s')
            ->from($this->getEntityName($object), 's')
            ->orderBy('s.id', isset($params['order']) ? $params['order'] : 'DESC');
        $adapter = new DoctrineORMAdapter($qb);
    } elseif ($object instanceof QueryBuilder) {
        $adapter = new DoctrineORMAdapter($object);
    }
    $pager = new Pagerfanta($adapter);
    $pager->setMaxPerPage(isset($params['perPage']) ? $params['perPage'] : 15);
    $pager->setCurrentPage(isset($params['curPage']) ? $params['curPage'] : 1);

    return $pager;
}

You can pass an array, entity or a query builder instance and it will return an appropriately paginated object ready to use.

You probably know how it's done, but anyway, here's what I have in my entity repository - one function returns query builder instance (perfect for pagerfanta), the other returns an array to be used elsewhere:

public function getMessageQueryBuilder($campaignId, $eqCriteriaArray = array(), $neqCriteriaArray = array())
{
    $qb = $this->createQueryBuilder('m');
    $qb->select('m')
        ->leftJoin('m.campaign', 'c')
        ->leftJoin('m.sentBy', 'u')
        ->where($qb->expr()->eq('m.campaign', $campaignId));
    foreach ($eqCriteriaArray as $property => $value) {
        $qb->andWhere($qb->expr()->eq($property, $qb->expr()->literal($value)));
    }
    foreach ($neqCriteriaArray as $property => $value) {
        $qb->andWhere($qb->expr()->neq($property, $qb->expr()->literal($value)));
    }

    return $qb->orderBy('m.id', 'DESC');
}

public function filterMessages($campaignId, $eqCriteriaArray = array(), $neqCriteriaArray = array())
{
    return $this->getMessageQueryBuilder($campaignId, $eqCriteriaArray, $neqCriteriaArray)->getQuery()->getResult();

Then I combine those two to get the actual pager object:

$singleSmsPager = $this->pagerUtil->paginate(
    $this->em->getRepository('TreasureForgeMessageBundle:Message')
        ->getMessageQueryBuilder(CcToolSender::CAMPAIGN_ID, array(), array('u.username' => 'admin')),
    array(
        'curPage' => $singleSmsPage,
        'perPage' => 10
    )
);

I don't think so. My queryBuilder (the one I pass to adapter) is only responsible for original select query. The way Pagerfanta creates `COUNT` query from it is different thing. — yefrem, Feb 22 '15 at 17:33
Sorry, I've checked my own pagination queries and they seem to do the same type of sub-querying. But then I haven't noticed any relation between performance and the count of records on my application. I would've thought mysql optimiser would limit the inner result set too in such cases. Interesting. — Vigintas Labakojis, Feb 22 '15 at 18:37
I've noticed problems when my table reached hundreds of thousands records. It also had high update rate so I think the result couldn't be cached. On other site I have about 2000 items paginated and updated once a day and see no problems — yefrem, Feb 22 '15 at 19:32

Pagerfanta and Doctrine2 COUNT optimization

2 Answers2