DDC-3646: Do not select unused columns in inner queries of Paginator #4480

New Issue

admin · 2026-01-22T14:42:34+01:00

admin commented

2026-01-22 14:42:34 +01:00

Originally created by @doctrinebot on GitHub (Mar 31, 2015).

Originally assigned to: @deeky666 on GitHub.

Jira issue originally created by user MalteWunsch:

When a Paginator count()s, it fires a query like this:

SELECT COUNT(*) AS dctrn*count FROM (SELECT DISTINCT id0 FROM (%originalQuery%) dctrn_result) dctrn*table

an getIterator() does something similar for joined collections to get the ids in the first place:

SELECT DISTINCT id0, lastmod11 FROM (%originalQuery%) dctrn_result ORDER BY lastmod11 DESC LIMIT 10 OFFSET 0

My problem is that both the inner queries can be too heavy for a regular temporary table.

At first sight I noticed several LEFT JOIN s that could be stripped from the inner query without changing the result of the outer one. But that might have been a specially easy case, a general optimisation of the JOIN clauses might be harder. I think http://www.doctrine-project.org/jira/browse/DDC-2381 deals with that.

On second thought we noticed some ugly BLOB columns as part of the projection, which unhealthily bloated the temporary table. Ultimately, the BLOBs are needed for the paginated view, but they are neither needed for counting nor for getting the paginated ids.

Hence, I propose to remove unused columns from the projection in the inner queries. "Unused" means not being part of the SELECT or ORDER BY clause in the outer query (and maybe not part of GROUP BY, HAVING... - haven't checked on this yet). The key could be the $innerSql in the LimitSubqueryOutputWalker->walkSelectStatement(), but before investigating further, I'd like to hear from you what you think.

Originally created by @doctrinebot on GitHub (Mar 31, 2015). Originally assigned to: @deeky666 on GitHub. Jira issue originally created by user MalteWunsch: When a `Paginator count()`s, it fires a query like this: ``` SELECT COUNT(*) AS dctrn*count FROM (SELECT DISTINCT id0 FROM (%originalQuery%) dctrn_result) dctrn*table ``` an `getIterator()` does something similar for joined collections to get the ids in the first place: ``` SELECT DISTINCT id0, lastmod11 FROM (%originalQuery%) dctrn_result ORDER BY lastmod11 DESC LIMIT 10 OFFSET 0 ``` My problem is that both the inner queries can be too heavy for a regular temporary table. At first sight I noticed several `LEFT JOIN` s that could be stripped from the inner query without changing the result of the outer one. But that might have been a specially easy case, a general optimisation of the `JOIN` clauses might be harder. I think http://www.doctrine-project.org/jira/browse/[DDC-2381](http://www.doctrine-project.org/jira/browse/DDC-2381) deals with that. On second thought we noticed some ugly BLOB columns as part of the projection, which unhealthily bloated the temporary table. Ultimately, the BLOBs are needed for the paginated view, but they are neither needed for counting nor for getting the paginated ids. Hence, I propose to remove unused columns from the projection in the inner queries. "Unused" means not being part of the `SELECT` or `ORDER BY` clause in the outer query (and maybe not part of `GROUP BY`, `HAVING`... - haven't checked on this yet). The key could be the `$innerSql` in the `LimitSubqueryOutputWalker->walkSelectStatement()`, but before investigating further, I'd like to hear from you what you think.

admin added the Improvement label 2026-01-22 14:42:34 +01:00

admin commented

2026-01-22 14:42:36 +01:00

@doctrinebot commented on GitHub (Mar 31, 2015):

Comment created by @ocramius:

Please review https://github.com/doctrine/doctrine2/pull/1353, as it might solve this issue

@doctrinebot commented on GitHub (Mar 31, 2015): Comment created by @ocramius: Please review https://github.com/doctrine/doctrine2/pull/1353, as it might solve this issue

admin commented

2026-01-22 14:42:37 +01:00

@doctrinebot commented on GitHub (Mar 31, 2015):

Comment created by MalteWunsch:

Thanks for your quick reply!

To be honest, I don't fully get the PR. At least it's goals look very different to me. Anyway, in LimitSubqueryOutputWalker the $innerSQL is now being generated in getInnerSQL(). In there, some hiddenAliasResultVariable are set to false (and are later restored) so that the parent SQLWalker can reference selected fields better. This might be a requirement for implementing my idea, but as far as I understand, there are no columns removed from the select clause so far?

@doctrinebot commented on GitHub (Mar 31, 2015): Comment created by MalteWunsch: Thanks for your quick reply! To be honest, I don't fully get the PR. At least it's goals look very different to me. Anyway, in `LimitSubqueryOutputWalker` the `$innerSQL` is now being generated in `getInnerSQL()`. In there, some `hiddenAliasResultVariable` are set to false (and are later restored) so that the parent SQLWalker can reference selected fields better. This might be a requirement for implementing my idea, but as far as I understand, there are no columns removed from the select clause so far?

admin commented

2026-01-22 14:42:38 +01:00

@doctrinebot commented on GitHub (Mar 31, 2015):

Comment created by @doctrinebot:

A related Github Pull-Request [GH-1353] was closed:
https://github.com/doctrine/doctrine2/pull/1353

@doctrinebot commented on GitHub (Mar 31, 2015): Comment created by @doctrinebot: A related Github Pull-Request [GH-1353] was closed: https://github.com/doctrine/doctrine2/pull/1353

admin commented

2026-01-22 14:42:38 +01:00

@doctrinebot commented on GitHub (Apr 6, 2015):

Comment created by @ocramius:

[~MalteWunsch] the PR is actually related as it affects the selected fields.

Consider checking again against 2.5.0, as we indeed fixed many paginator issues, and this issue needs to be re-validated.

@doctrinebot commented on GitHub (Apr 6, 2015): Comment created by @ocramius: [~MalteWunsch] the PR is actually related as it affects the selected fields. Consider checking again against `2.5.0`, as we indeed fixed many paginator issues, and this issue needs to be re-validated.

admin commented

2026-01-22 14:42:38 +01:00

@doctrinebot commented on GitHub (Apr 7, 2015):

Comment created by MalteWunsch:

Great! I'll be glad to check. It might take some time, as we currently only have PHP 5.3 at our disposal (please don't tell anybody), and Doctrine 2 ORM 2.5.0 requires at least PHP 5.4. Fortunately, this is a very good trigger to put some pressure on the PHP upgrade process. I'll keep my eye on it.

@doctrinebot commented on GitHub (Apr 7, 2015): Comment created by MalteWunsch: Great! I'll be glad to check. It might take some time, as we currently only have PHP 5.3 at our disposal (please don't tell anybody), and Doctrine 2 ORM 2.5.0 requires at least PHP 5.4. Fortunately, this is a very good trigger to put some pressure on the PHP upgrade process. I'll keep my eye on it.

admin commented

2026-01-22 14:42:39 +01:00

@doctrinebot commented on GitHub (Apr 10, 2015):

Comment created by MalteWunsch:

[~ocramius] Unfortunately, Doctrine 2.5 had no effect on my inner original query. It still looks like this:

SELECT 
  COUNT(*) AS dctrn_count 
FROM 
  (
    SELECT 
      DISTINCT id0 
    FROM 
      (
        SELECT 
          [all fields from t0_],
          [all fields from t1_],
          [all fields from t2_],
          [all fields from t3_]
        FROM 
          table1 t0_ 
          LEFT JOINs...
        WHERE 
          ...
        ORDER BY 
          ...
      ) dctrn_result
  ) dctrn_table

Just to be clear, my point is with the most inner query. If I'm correct it is generated in LimitSubqueryOutputWalker->getInnerSql(). In the special case with the query above, one could simplify it by dropping the order clause, removing the left joins and selecting only the id0 field. I have no plan for inner joins and other more complex optimisations, but I guess that removing the unused fields from the select clause might be in reach.

@doctrinebot commented on GitHub (Apr 10, 2015): Comment created by MalteWunsch: [~ocramius] Unfortunately, Doctrine 2.5 had no effect on my inner original query. It still looks like this: ``` SELECT COUNT(*) AS dctrn_count FROM ( SELECT DISTINCT id0 FROM ( SELECT [all fields from t0_], [all fields from t1_], [all fields from t2_], [all fields from t3_] FROM table1 t0_ LEFT JOINs... WHERE ... ORDER BY ... ) dctrn_result ) dctrn_table ``` Just to be clear, my point is with the most inner query. If I'm correct it is generated in [LimitSubqueryOutputWalker->getInnerSql()](https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Tools/Pagination/LimitSubqueryOutputWalker.php#L482). In the special case with the query above, one could simplify it by dropping the order clause, removing the left joins and selecting only the id0 field. I have no plan for inner joins and other more complex optimisations, but I guess that removing the unused fields from the select clause might be in reach.

admin commented

2026-01-22 14:42:39 +01:00

@doctrinebot commented on GitHub (Apr 10, 2015):

Comment created by mpdude:

When removing fields from the SELECT clause, we need to keep those that are referenced from GROUP BY or HAVING clauses (when ORDER BY is dropped).

@doctrinebot commented on GitHub (Apr 10, 2015): Comment created by mpdude: When removing fields from the SELECT clause, we need to keep those that are referenced from GROUP BY or HAVING clauses (when ORDER BY is dropped).

admin commented

2026-01-22 14:42:40 +01:00

@doctrinebot commented on GitHub (Apr 14, 2015):

Comment created by mpdude:

[http://www.doctrine-project.org/jira/browse/DDC-2381] seems to be related or identical.

@doctrinebot commented on GitHub (Apr 14, 2015): Comment created by mpdude: [http://www.doctrine-project.org/jira/browse/[DDC-2381](http://www.doctrine-project.org/jira/browse/DDC-2381)] seems to be related or identical.

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#4480