Speeding up SQL queries by orders of magnitude using UNION
This is ABC of SQL: every SQL developer has to learn this lesson soon or later: Every additional JOIN you make creates a cartesian product of all previously joined records and the number of rows grows exponentially => you join tables A to B to C to D -> SQL engine has to create AxBxCxD number rows, each letter representing number of rows in that table)<p>Of course specifying useful predicates that filter down dataset and having indexes on these columns helps avoid row explosion, but you can't have an index on each and every column, especially on OLTP database.<p>in example #2 the author joined 7 tables (multiply row count of 7 tables and this gives you an indea of how many rows SQL engine has to churn through) - big O complexity of query is (AxBxCxDxExFxG) of course it will be SLOW.<p>in example #4 he joins 4 tables and unions with 5 table join, so the big O complexity of query is AxBxCxD(1+E).<p>same as in programming, there is Big O complexity in your SQL queries, so it helps to know O() compelxity of queries that you write<p>TLDR: learn the SQL Big-O and stop compaining about the query planner, pls