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?
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With