Search code examples
mysqlquery-optimization

Why is MySQL Derived Condition Pushdown Optimization not working

Can anyone explain why MySQL Derived Condition Pushdown Optimization https://dev.mysql.com/doc/refman/8.0/en/derived-condition-pushdown-optimization.html is not happening for this query?

SELECT *
      FROM (SELECT ST.SERVICE_TICKET_ID,
                 ST.SERVICE_CATEGORY_ID,
                 ST.STATUS_ID,
                 EC.BRANCH_ID,
                 ST.PRICE,
                 GROUP_CONCAT(STTPER.FIRST_NAME, ' ', STTPER.LAST_NAME ORDER BY STTPER.LAST_NAME, STTPER.FIRST_NAME SEPARATOR ', ')
          FROM SERVICE_TICKET ST
                   LEFT OUTER JOIN ELEMENT_CUSTOMER EC ON ST.CUSTOMER_ID = EC.PARTY_ID
                   LEFT OUTER JOIN SERVICE_TICKET_TECHNICIAN STT
                                   ON ST.SERVICE_TICKET_ID = STT.SERVICE_TICKET_ID AND (NOT STT.INACTIVE <=> 1)
                   LEFT OUTER JOIN PERSON STTPER ON STT.TECHNICIAN_ID = STTPER.PARTY_ID
           WHERE ((ST.SERVICE_TICKET_CATEGORY_ID = 'SERVICE' AND ST.STATUS_ID <> 'SVC_TICK_DEACTIVATED'))
          GROUP BY ST.SERVICE_TICKET_ID ) REPORT
WHERE (((REPORT.BRANCH_ID IN ('10081') AND REPORT.SERVICE_CATEGORY_ID = '10080')))

It seems to meet the qualification for optimization

When the derived table has a GROUP BY and uses no window functions, an outer WHERE condition referencing one or more columns which are not part of the GROUP BY can be pushed down to the derived table as a HAVING condition.

The problem is definitely related to GROUP BY, because it works when I remove it. It doesn't matter whether I try GROUP_CONCAT() or COUNT(), so it doesn't seem to be related to the specific aggregate function.

Here's the EXPLAIN

enter image description here

Yes, the optimizer-switch setting for derived-condition-pushdown is 'on', as is evidenced by it working when the GROUP BY is removed.

If I can understand WHY it's not optimizing, perhaps I can craft a workaround.

Solution

  • The documentation about derived condition pushdown covers two cases related to subqueries with aggregation:

    • "When the derived table has a GROUP BY and uses no window functions, an outer WHERE condition referencing one or more columns which are not part of the GROUP BY can be pushed down to the derived table as a HAVING condition."

      But the example shows only references to aggregated columns.

    • "When the derived table uses a GROUP BY and the columns in the outer WHERE condition are GROUP BY columns, the WHERE conditions referencing those columns can be pushed down directly to the derived table."

    However, your query shows a different case: the columns you reference in the outer query are neither aggregated nor in the GROUP BY of the inner query. I assume they are functionally dependent on the column in the GROUP BY.

    As you found out by experimenting, if you explicitly list these columns in your GROUP BY in the subquery, then it allows derived condition pushdown to work.