AnsweredAssumed Answered

How can I optimize my MySQL query using sum / inner join?

Question asked by Pete Baumann on Jun 19, 2014
Latest reply on Jun 26, 2014 by Chris Hall
we have the following table structure (within sugar):

Accounts: id | name .....

transactions: account_id | order_year | amount ....

our database is already very big (>300.000) accounts and transactions > 1.000.000

in our accounts list we now need a filter to search for accounts that
  had in sum of the amount more than XXXXEUR within year YYYY.

we know how to implement the filters within sugar and also how to customize the queries but the sql queries itself are the problem......

we already tried with subselect(inner Join and sum but our queries were much too slow.

maybe anybody can give us some hints about how to make this SQL query regarding performance (regardless of sugarcrm. only the plain SQL queries regarding the upper table Structure.

thanks Tom