Paginator/groupBy seems very slow #5276

New Issue

admin · 2026-01-22T15:03:14+01:00

admin commented

2026-01-22 15:03:14 +01:00

Originally created by @zstiggz on GitHub (Sep 28, 2016).

Originally assigned to: @Ocramius on GitHub.

Originally posted this question on StackOverflow:
http://stackoverflow.com/questions/39736173/doctrine-paginator-selects-entire-table-very-slow

This is related to a previous question here:
http://stackoverflow.com/questions/39721673/doctrine-symfony-query-builder-add-select-on-left-join

I want to perform a complex join query using Doctrine ORM. I want to select 10 paginated blog posts, left joining a single author, like value for current user, and hashtags on the post. My query builder looks like this:

$query = $em->createQueryBuilder()
            ->select('p')              
            ->from('Post', 'p')
            ->leftJoin('p.author', 'a')
            ->leftJoin('p.hashtags', 'h')
            ->leftJoin('p.likes', 'l', 'WITH', 'l.post_id = p.id AND l.user_id = 10')
            ->where("p.foo = bar")
            ->addSelect('a AS post_author')
            ->addSelect('l AS post_liked')
            ->addSelect('h AS post_hashtags')
            ->setFirstResult(0)
            ->setMaxResults(10);

// FAILS - because left joined hashtag collection breaks LIMITS
$result = $query->getQuery()->getResult(); 

// WORKS - but is extremely slow (count($result) shows over 80,000 rows)
$result = new \Doctrine\ORM\Tools\Pagination\Paginator($query, true);

Strangely, count($result) on the paginator shows the total number of rows in my table (over 80,000) but traversing the $result with foreach outputs 10 Post entities, as expected. Do I need to do some additional configuration to properly limit my paginator?

If this is a limitation of the paginator class what other options do I have? Writing custom paginator code or other paginator libraries?

EDIT (bonus): How can I hydrate an array, like $query->getQuery()->getArrayResult();?

Things I tried:

// instead of paginator, use groupBy - works but makes the query about 50x slower
$query->groupBy('p.id');
$result = $query->getQuery()->getArrayResult();

Originally created by @zstiggz on GitHub (Sep 28, 2016). Originally assigned to: @Ocramius on GitHub. Originally posted this question on StackOverflow: http://stackoverflow.com/questions/39736173/doctrine-paginator-selects-entire-table-very-slow This is related to a previous question here: http://stackoverflow.com/questions/39721673/doctrine-symfony-query-builder-add-select-on-left-join I want to perform a complex join query using Doctrine ORM. I want to select 10 paginated blog posts, left joining a single author, like value for current user, and hashtags on the post. My query builder looks like this: ``` php $query = $em->createQueryBuilder() ->select('p') ->from('Post', 'p') ->leftJoin('p.author', 'a') ->leftJoin('p.hashtags', 'h') ->leftJoin('p.likes', 'l', 'WITH', 'l.post_id = p.id AND l.user_id = 10') ->where("p.foo = bar") ->addSelect('a AS post_author') ->addSelect('l AS post_liked') ->addSelect('h AS post_hashtags') ->setFirstResult(0) ->setMaxResults(10); // FAILS - because left joined hashtag collection breaks LIMITS $result = $query->getQuery()->getResult(); // WORKS - but is extremely slow (count($result) shows over 80,000 rows) $result = new \Doctrine\ORM\Tools\Pagination\Paginator($query, true); ``` Strangely, count($result) on the paginator shows the total number of rows in my table (over 80,000) but traversing the $result with foreach outputs 10 Post entities, as expected. Do I need to do some additional configuration to properly limit my paginator? If this is a limitation of the paginator class what other options do I have? Writing custom paginator code or other paginator libraries? EDIT (bonus): How can I hydrate an array, like $query->getQuery()->getArrayResult();? Things I tried: ``` php // instead of paginator, use groupBy - works but makes the query about 50x slower $query->groupBy('p.id'); $result = $query->getQuery()->getArrayResult(); ```

admin closed this issue

2026-01-22 15:03:14 +01:00

admin commented

2026-01-22 15:03:14 +01:00

@Ocramius commented on GitHub (Sep 28, 2016):

Strangely, count($result) on the paginator shows the total number of rows in my table (over 80,000) but traversing the $result with foreach outputs 10 Post entities, as expected. Do I need to do some additional configuration to properly limit my paginator?

The paginator counts the total (without limits) for the selection you have, not just the 10 you get.

If this is a limitation of the paginator class what other options do I have? Writing custom paginator code or other paginator libraries?

Custom DQL, probably. You can also simply use min(count($paginator), $query->getMaxResults()) to count the current selection.

// instead of paginator, use groupBy - works but makes the query about 50x slower

The paginator doesn't use GROUP BY, but rather an IN() subquery.

We can't directly help with the GROUP BY slowness, but I suggest passing it through a query planner, and seeing what is going on. the IN() subquery acts on the primary key, so we have at least some sort of direct hit on an existing index.

Closing, since this is not an issue, but a question.

@Ocramius commented on GitHub (Sep 28, 2016): > Strangely, count($result) on the paginator shows the total number of rows in my table (over 80,000) but traversing the $result with foreach outputs 10 Post entities, as expected. Do I need to do some additional configuration to properly limit my paginator? The paginator counts the total (without limits) for the selection you have, not just the 10 you get. > If this is a limitation of the paginator class what other options do I have? Writing custom paginator code or other paginator libraries? Custom DQL, probably. You can also simply use `min(count($paginator), $query->getMaxResults())` to count the current selection. > // instead of paginator, use groupBy - works but makes the query about 50x slower The paginator doesn't use `GROUP BY`, but rather an `IN()` subquery. We can't directly help with the `GROUP BY` slowness, but I suggest passing it through a query planner, and seeing what is going on. the `IN()` subquery acts on the primary key, so we have at least some sort of direct hit on an existing index. Closing, since this is not an issue, but a question.

admin commented

2026-01-22 15:03:16 +01:00

@zstiggz commented on GitHub (Sep 28, 2016):

@Ocramius, thanks for the response!

Yeah, this was more of a question - I suppose the "issue" was more unexpected behavior of the paginator. I would have expected $query->getResult() and new Paginator($query) to have comparable performance without the addSelects, but found the paginator to be much slower - regardless of whether or not I add leftJoins and/or addSelects to my query.

At any rate, thanks for the tips. I'm still pretty stumped though - is there a doctrine message board or something where I might go to probe this question further?

@zstiggz commented on GitHub (Sep 28, 2016): @Ocramius, thanks for the response! Yeah, this was more of a question - I suppose the "issue" was more unexpected behavior of the paginator. I would have expected `$query->getResult()` and `new Paginator($query)` to have comparable performance without the addSelects, but found the paginator to be much slower - regardless of whether or not I add leftJoins and/or addSelects to my query. At any rate, thanks for the tips. I'm still pretty stumped though - is there a doctrine message board or something where I might go to probe this question further?

admin commented

2026-01-22 15:03:16 +01:00

@Ocramius commented on GitHub (Sep 28, 2016):

We have doctrine-user, but stackoverflow is quite efficient at this already.

On 28 Sep 2016 05:07, "Z" notifications@github.com wrote:

@Ocramius https://github.com/Ocramius, thanks for the response!

Yeah, this was more of a question - I suppose the "issue" was more
unexpected behavior of the paginator. I would have expected
$query->getResult() and new Paginator($query) to have comparable
performance without the addSelects, but found the paginator to be much
slower - regardless of whether or not I add leftJoins and/or addSelects to
my query.

At any rate, thanks for the tips. I'm still pretty stumped though - is
there a doctrine message board or something where I might go to probe this
question further?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/doctrine/doctrine2/issues/6057#issuecomment-250060378,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAJakHeFzbwD99ZrwrH9nE65ml9-SeuJks5qudnsgaJpZM4KIWsU
.

@Ocramius commented on GitHub (Sep 28, 2016): We have doctrine-user, but stackoverflow is quite efficient at this already. On 28 Sep 2016 05:07, "Z" notifications@github.com wrote: > @Ocramius https://github.com/Ocramius, thanks for the response! > > Yeah, this was more of a question - I suppose the "issue" was more > unexpected behavior of the paginator. I would have expected > $query->getResult() and new Paginator($query) to have comparable > performance without the addSelects, but found the paginator to be much > slower - regardless of whether or not I add leftJoins and/or addSelects to > my query. > > At any rate, thanks for the tips. I'm still pretty stumped though - is > there a doctrine message board or something where I might go to probe this > question further? > > — > You are receiving this because you were mentioned. > Reply to this email directly, view it on GitHub > https://github.com/doctrine/doctrine2/issues/6057#issuecomment-250060378, > or mute the thread > https://github.com/notifications/unsubscribe-auth/AAJakHeFzbwD99ZrwrH9nE65ml9-SeuJks5qudnsgaJpZM4KIWsU > .

admin commented

2026-01-22 15:03:16 +01:00

@zstiggz commented on GitHub (Sep 28, 2016):

Roger that. I'll cross my fingers and hope something pans out on my stackoverflow questions.

@zstiggz commented on GitHub (Sep 28, 2016): Roger that. I'll cross my fingers and hope something pans out on my stackoverflow questions.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5276