Optimizer in 8.0 in some cases not materializing subquery
We have a query that went from taking 2 seconds in MySQL 5.7 to 50+ seconds in 8.0. It appears it has to do with how the optimizer is handling some subqueries.
Example of query:
SELECT e.id, e.status_id, COUNT(e.id) AS cnt
FROM Events e
WHERE e.ent_id IN (1)
AND
NOT EXISTS
(
SELECT eam.account
FROM EventAccountMapping eam
WHERE
eam.event_id = e.id AND
eam.account NOT IN
(
SELECT uapt.account
FROM UserAccountPermissions uapt
WHERE uapt.user_id = 1
) /* Subquery 2 */
)
In 5.7, it was treating Subquery 2 as a subquery, and in 5.8, it's be treated as a dependent subquery, even though it is not dependent on the other tables
If I break this into 2 queries where the select from UserAccountPermissions saves the results in a temp table, and then use that temp table, it goes from 50 seconds to 2. If I actually generate the results fo subquery 2, and inline the results ( 'aaa', 'bbbb', 'cccc', etc), the query goes down to less than one second.
In 5.8, I moved the subquery 2 to a CTE, and that doesn't resolve the issue either. Is there a way to force a CTE to get materialized?
Subject
Views
Written By
Posted
Optimizer in 8.0 in some cases not materializing subquery
707
January 24, 2022 03:37PM
301
January 24, 2022 04:45PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.