Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pagerfanta and Doctrine2 COUNT optimization

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?

like image 568
yefrem Avatar asked Apr 18 '14 17:04

yefrem


1 Answers

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

like image 198
Vigintas Labakojis Avatar answered Sep 27 '22 00:09

Vigintas Labakojis