LimitSubqueryWalker performance issue with DISTINCT #7114

New Issue

admin · 2026-01-22T15:44:58+01:00

admin commented

2026-01-22 15:44:58 +01:00

Originally created by @goetas on GitHub (Mar 1, 2023).

Feature Request

Q	A
New Feature	yes
RFC	yes
BC Break	no

Summary

\Doctrine\ORM\Tools\Pagination\LimitSubqueryWalker Adds a DISTINCT keyword to paginate with no side effects queries that potentially contain multiple occurrences because of a join table that adds additional rows...

See f82485e651/lib/Doctrine/ORM/Tools/Pagination/LimitSubqueryWalker.php (L69)

The DISTINCT might be expensive in some cases.

In some cases it is known (not by doctrine but by the developer writing the query) that despite having joins, they are not adding extra rows, thus DISTINCT will not remove any row.

I would like to add a way into LimitSubqueryWalker to not use the distinct keyword.

Would it be acceptable something as this:

$AST->selectClause->isDistinct  =  !$query->hasHint('opt_out_distinct') || !$query->getHint('opt_out_distinct');

in this way when writing the query, it would be possible to set the opt_out_distinct to skip the DISTINCT.

$query->setHint('opt_out_distinct', true);

What do you think? is it acceptable?

A similar issue was reported in API platform in https://github.com/api-platform/api-platform/issues/792

If yes i can prepare a PR.

Originally created by @goetas on GitHub (Mar 1, 2023). ### Feature Request | Q | A |------------ | ------ | New Feature | yes | RFC | yes | BC Break | no #### Summary `\Doctrine\ORM\Tools\Pagination\LimitSubqueryWalker` Adds a `DISTINCT` keyword to paginate with no side effects queries that potentially contain multiple occurrences because of a join table that adds additional rows... See https://github.com/doctrine/orm/blob/f82485e651763fbd1b34879726f4d3b91c358bd9/lib/Doctrine/ORM/Tools/Pagination/LimitSubqueryWalker.php#L69 The `DISTINCT` might be expensive in some cases. In some cases it is known (not by doctrine but by the developer writing the query) that despite having joins, they are not adding extra rows, thus `DISTINCT` will not remove any row. I would like to add a way into `LimitSubqueryWalker` to not use the distinct keyword. Would it be acceptable something as this: ```php $AST->selectClause->isDistinct = !$query->hasHint('opt_out_distinct') || !$query->getHint('opt_out_distinct'); ``` in this way when writing the query, it would be possible to set the `opt_out_distinct` to skip the `DISTINCT`. ```php $query->setHint('opt_out_distinct', true); ``` What do you think? is it acceptable? A similar issue was reported in API platform in https://github.com/api-platform/api-platform/issues/792 If yes i can prepare a PR.

admin closed this issue

2026-01-22 15:44:59 +01:00

admin commented

2026-01-22 15:44:59 +01:00

@nkrovex commented on GitHub (Sep 29, 2023):

This opportunity must be present. I came across a similar issue.

@nkrovex commented on GitHub (Sep 29, 2023): This opportunity must be present. I came across a similar issue.

admin commented

2026-01-22 15:45:00 +01:00

@beberlei commented on GitHub (Sep 30, 2023):

@goetas it would be fine to have this yes, a query hint would be a good way. Make sure to add it as constant on the paginator and prefix with paginate, paginate_disable_distinct for example

@beberlei commented on GitHub (Sep 30, 2023): @goetas it would be fine to have this yes, a query hint would be a good way. Make sure to add it as constant on the paginator and prefix with paginate, paginate_disable_distinct for example

admin commented

2026-01-22 15:45:00 +01:00

@goetas commented on GitHub (Oct 7, 2023):

Here you go https://github.com/doctrine/orm/pull/10970

@goetas commented on GitHub (Oct 7, 2023): Here you go https://github.com/doctrine/orm/pull/10970

admin commented

2026-01-22 15:45:01 +01:00

@goetas commented on GitHub (Oct 9, 2023):

https://github.com/doctrine/orm/pull/10970 has been merged

@goetas commented on GitHub (Oct 9, 2023): https://github.com/doctrine/orm/pull/10970 has been merged

admin referenced this issue

2026-01-22 16:07:10 +01:00