“Let me once again explain the rules. Tera-Tom Rules!”
1. When given a choice between using a join or a subquery, use the join.
2. Change your expression's subselects by moving the subselect from the WHERE clause to the FROM clause or by rewriting it as a join.
3. Use an inner subquery to shrink the size of data on which outer queries needs to operate. Likewise, use an inner subquery to shrink the size of a relation before the data is redistributed on a different distribution key.
4. Avoid per-row functions or delay them. Group rows first, and then apply the function.
5. Avoid doing JOINs, GROUP BYs, and WHERE clauses on data types that are non-native.
6. EXPLAIN should be used ...