MySQL Forums
Forum List  »  Optimizer & Parser

Optimizer in 8.0 in some cases not materializing subquery
Posted by: Benjamin Peikes
Date: January 24, 2022 03:37PM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimizer in 8.0 in some cases not materializing subquery
707
January 24, 2022 03:37PM


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.