I have a query that takes 10-20 seconds to compile and runs in <5 seconds once compiled.
Is it possible to get the Engine’s Optimized version of the SQL to use as a reference for optimising the orignal query ?
The query optimizer takes the original query and builds an internal algebraic structure from it before it starts optimizing the query. It optimizes by building up from smaller to larger trees, and doing tree transformations. So there really is not SQL representation of the optimized query. However, you can look at the EXPLAIN and PROFILE plans to see the shape the optimizer chose. Check out the graphical EXPLAIN and PROFILE in our managed service portal or in SingleStore Studio. That’s the easiest to read.