Comment by refset
> just unrolling several unnecessary nested subqueries, and adding a more selective predicate
And state of the art query optimizers can even do all this automatically!
> just unrolling several unnecessary nested subqueries, and adding a more selective predicate
And state of the art query optimizers can even do all this automatically!
Sometimes, yes. Sometimes not. This was on MySQL 5.7, and I wound up needing to trace the optimizer path to figure out why it was slower than expected.
While I do very much appreciate things like WHERE foo IN —> WHERE EXISTS being automatically done, I also would love it if devs would just write the latter form. Planners are fickle, and if statistics get borked, query plans can flip. It’s much harder to diagnose when all along, the planner has been silently rewriting your query, and only now is actually running it as written.