Paginate method performs poorly when used with inadequate WHERE conditions #7002

New Issue

admin · 2026-01-22T15:42:56+01:00

admin commented

2026-01-22 15:42:56 +01:00

Originally created by @kazamaku on GitHub (Jul 14, 2022).

I used version V2.7.5

I've found the Paginate method performs poorly when used with inadequate WHERE conditions

SELECT DISTINCT
  id_51 
FROM
  (
  SELECT DISTINCT
    id_51,
    created_at_7 
  FROM
    (
    SELECT
      c0_.user_id AS user_id_0,
      c0_.business_code AS business_code_4,
      c0_.order_no AS order_no_6,
      c0_.created_at AS created_at_7,
      c0_.updated_at AS updated_at_8,
      c0_.deleted_at AS deleted_at_9,
      c0_.id AS id_51 
    FROM
      common_order c0_
      LEFT JOIN common_order_product c1_ ON c0_.id = c1_.order_id 
      AND ( c1_.deleted_at IS NULL ) 
    WHERE
      ( c0_.business_code = 'OAO' ) 
    AND ( c0_.deleted_at IS NULL )) dctrn_result_inner 
  ORDER BY
    created_at_7 DESC,
    id_51 DESC 
  ) dctrn_result 
  LIMIT 10;

I'm using MYSQL with a lot of data,The Paginate method automatically uses DISTINCT
After the test, the query time using DISTINCT was 80 seconds, and it only took 0.02 seconds without using DISTINCT
JOIN does create multiple pieces of data,so I try delete leftjoin common_order_product, but DISTINCT will still be used

I found it in the source code, This indicates that DISTINCT is used regardless of whether a JOIN is used ⬇️

return $sql = \sprintf(
            'SELECT DISTINCT %s FROM (%s) dctrn_result_inner ORDER BY %s',
            \implode(', ', $identifiers),
            $innerSql,
            \implode(', ', $orderByItems)
        );

So，I modified the source code ⬇️

if (count($this->queryComponents) > 1) {
    return $sql = \sprintf(
        'SELECT DISTINCT %s FROM (%s) dctrn_result_inner ORDER BY %s',
        \implode(', ', $identifiers),
        $innerSql,
        \implode(', ', $orderByItems)
    );
} else {
    return $sql = \sprintf(
        'SELECT %s FROM (%s) dctrn_result_inner ORDER BY %s',
        \implode(', ', $identifiers),
        $innerSql,
        \implode(', ', $orderByItems)
    );
}

But I don't know if this revision will affect other scenarios

Originally created by @kazamaku on GitHub (Jul 14, 2022). I used version V2.7.5 I've found the Paginate method performs poorly when used with inadequate WHERE conditions ``` SELECT DISTINCT id_51 FROM ( SELECT DISTINCT id_51, created_at_7 FROM ( SELECT c0_.user_id AS user_id_0, c0_.business_code AS business_code_4, c0_.order_no AS order_no_6, c0_.created_at AS created_at_7, c0_.updated_at AS updated_at_8, c0_.deleted_at AS deleted_at_9, c0_.id AS id_51 FROM common_order c0_ LEFT JOIN common_order_product c1_ ON c0_.id = c1_.order_id AND ( c1_.deleted_at IS NULL ) WHERE ( c0_.business_code = 'OAO' ) AND ( c0_.deleted_at IS NULL )) dctrn_result_inner ORDER BY created_at_7 DESC, id_51 DESC ) dctrn_result LIMIT 10; ``` I'm using MYSQL with a lot of data,The Paginate method automatically uses DISTINCT After the test, the query time using DISTINCT was 80 seconds, and it only took 0.02 seconds without using DISTINCT JOIN does create multiple pieces of data,so I try delete leftjoin common_order_product, but DISTINCT will still be used I found it in the source code, This indicates that DISTINCT is used regardless of whether a JOIN is used ⬇️ ``` return $sql = \sprintf( 'SELECT DISTINCT %s FROM (%s) dctrn_result_inner ORDER BY %s', \implode(', ', $identifiers), $innerSql, \implode(', ', $orderByItems) ); ``` So，I modified the source code ⬇️ ``` if (count($this->queryComponents) > 1) { return $sql = \sprintf( 'SELECT DISTINCT %s FROM (%s) dctrn_result_inner ORDER BY %s', \implode(', ', $identifiers), $innerSql, \implode(', ', $orderByItems) ); } else { return $sql = \sprintf( 'SELECT %s FROM (%s) dctrn_result_inner ORDER BY %s', \implode(', ', $identifiers), $innerSql, \implode(', ', $orderByItems) ); } ``` But I don't know if this revision will affect other scenarios

admin commented

2026-01-22 15:42:56 +01:00

@kazamaku commented on GitHub (Aug 3, 2022):

After the modification is complete, you find that the sorting is invalid

So I'm going to change it again

        if (count($this->queryComponents) > 1) {
            return \sprintf(
                'SELECT DISTINCT %s FROM (%s) dctrn_result',
                \implode(', ', $sqlIdentifier),
                $this->recreateInnerSql($orderByClause, $sqlIdentifier, $innerSql)
            );
        } else {
//            return \sprintf(
//                'SELECT %s FROM (%s) dctrn_result',
//                \implode(', ', $sqlIdentifier),
//                $this->recreateInnerSql($orderByClause, $sqlIdentifier, $innerSql)
//            );
            return $this->recreateInnerSql($orderByClause, $sqlIdentifier, $innerSql);
        }

I go under certain conditions except for the outermost query

It's been online for 20 days now , It's normal at least now.

@kazamaku commented on GitHub (Aug 3, 2022): After the modification is complete, you find that the sorting is invalid So I'm going to change it again ``` if (count($this->queryComponents) > 1) { return \sprintf( 'SELECT DISTINCT %s FROM (%s) dctrn_result', \implode(', ', $sqlIdentifier), $this->recreateInnerSql($orderByClause, $sqlIdentifier, $innerSql) ); } else { // return \sprintf( // 'SELECT %s FROM (%s) dctrn_result', // \implode(', ', $sqlIdentifier), // $this->recreateInnerSql($orderByClause, $sqlIdentifier, $innerSql) // ); return $this->recreateInnerSql($orderByClause, $sqlIdentifier, $innerSql); } ``` I go under certain conditions except for the outermost query It's been online for 20 days now , It's normal at least now.

admin referenced this issue

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

[PR #7054] Fix ID generation of foreign keys #10294

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