In MySQL, the same query thrashes really hard. After some experimentation, it seems like the subquery itself is the problem. For instance: ...

MySQL Autopilot uses advanced machine learning techniques to accelerate HeatWave query performance and scalability in Oracle Cloud’s MySQL Database Service.