A Tale of Query Optimization
It isn't clear to me why so few of these optimization articles talk about temporary tables. They are my go-to solution for many situations.<p>Temporary tables are great because they break the problem down into smaller pieces. They make the query much more understandable. They are way easier to debug. They are way less sensitive to failing because the query optimizer decides to do things differently because table statistics have changed. They will often eliminate the need for exotic syntax.<p>You also have the opportunity to add secondary indexes to these tables.<p>When you create a temporary table, you are essentially doing the work of the optimizer yourself. That's perfectly ok if you know things about the data that the optimizer doesn't or hasn't successfully figured out on its own.<p>They're not for every situation; they only work well when the intermediate tables are small and fit in memory. But that is very frequently the case.