I'm planning to redesign the structure of a query which uses over 20 full/left joins for fetching different data from the tables.
Because the logic of the initial query was to bring data based on some conditions, not all the joins were needed if a certain condition is satisfied.
For example : The query has 5 different conditions, each condition satisfies a certain scenario where only some table joins are required.
INITIAL QUERY EXAMPLE :
Select -- fields From table1 t1 Join table 2 t2 ON t1.id = t2.id ... Join table n tn+1 ON ..... Where (t1.field1 = 1 and t1.field2 = 0) AND ( (t2.type = 1 and somefield = value and somefield2 = value ... somefield n+1 = value ) OR (t2.type = 2 and (somefield3 = value or somefield4=value or .... somefield n+1 = value) ) ....... OR (t2.type = n and (somefield n...n+1 = value)))
Note: The fields on which the conditions are written are never changed, the conditions are exactly as those written in the example above.
My thoughts are to redesign the structure of the query by splitting it into multiple queries joined together with
UNION ALL. By doing this, I would get rid of the entire amount of joins for each condition.
I'm not sure if this could bring any real benefits mentioning the fact that in some cases when a certain condition is being satisfied , some of the table joins are on
null values because the type of the conditions makes the query to bring a certain data which doesn't have the data the join needs.
The real questions would be :
- Splitting the query into UNIONS would be better/faster design ?
- Which is the pattern in the query for making an
INDEX? If so, how should I create the index, which fields from the given example?