Creating Temporary Join Tables [Performance]
If you use similar joins multiple times, create temporary tables that cover (sub-) joins and use them instead of executing the same join statements repeatedly.
Example - Step 1: Creating Temporary Join Table
DROP TABLE IF EXISTS temp2; CREATE TABLE temp2 AS SELECT ... FROM ANY_TABLE JOIN ANOTHER_TABLE ON 1=1 AND ANY_TABLE.ID = ANOTHER_TABLE.ID ;
Example - Step 2: Replacing Repeated Join Statements With Temporary Join Table
SELECT ... FROM temp2 WHERE 1=1 AND any_column IN ('example') AND any_column <> 0 ; SELECT ... FROM temp2 JOIN SECOND_TABLE ON 1=1 AND temp2.ID = SECOND_TABLE.ID AND SECOND_TABLE.any_column IN ('example1','example2') AND temp2.any_column <> 0 ;