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?

Answer 1

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