Calculate quantity in a faster way than in this query

Question

I need to calculate total available qty from the database, and for that I need to do joins with a couple of tables. I can not paste my whole query, but the following is the basic structure:

select sum(qty) as qty, field
from
(
  (
      select SUM(table1.qty) as qty , field
       from
       table1
       left join table2 on table1.field = table2.field
       left join table3 on table3.field = table2.field
       where condition
       group by fieldname
   )
   UNION ALL
   (
       selecy SUM(table1.qty) as qty,field 
       from
       table1
       left join table2 on table1.field = table2.field
       left join table3 on table3.field = table2.field
       where condition
       group by fieldname
   )
   UNION ALL
   (
       select SUM(table1.qty) as qty, field
       from
       table1
       left join table2 on table1.field = table2.field
       left join table3 on table3.field = table2.field
       where condition
       group by fieldname
   )
  ...
  ..
  12 times
) as temp
LEFT JOIN another_main_table ON another_main_table.field = temp.field

I have taken care of indexes of each table, but there are some unions which are taking longer time than expected. There are around 45 tables used in this query and all are examined fully. Some tables have around 2.6 million records. Can you please suggest me how I can get the result in 1/2 seconds? As of now I am getting the result in around one minute.

Answer 1

Since your given example one can not properly justify the proper solutions, but still if I roughly examine your query, you have used LEFT JOIN, So this will take a longer time compare to INNER JOIN.

So, Use INNER JOIN if your data permits